我有两个表GOODS为商品,有GOODS_ID,GOODS_NUM,GOODS_NAME,CAUTION_NUM当GOODS_NUM的值<CAUTION_NUM的值的时候,我就向名为INDENT的表中插入GOODS_ID,GOODS_NUM,GOODS_NAME,INDENT_DATE,INDENT_NUM其中INDENT_NUM为创建的序列IDENTSEQUENCE生成的序列的值。请问这个触发器怎么写?获取系统当前时间是 select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual
referencing old row as orow,new row as nrow
for each row
when orow.GOODS_NUM>CAUTION_NUM and nrow.GOODS_NUM<=CAUTION_NUM
begin
insert into INDENT
values(nrow.GOODS_ID,nrow.GOODS_NAME,nrow.GOODS_NUM,select tochar(sysdate,'yyyy mm dd hh24:mi:ss') from dual,INDENTSEQUENCE.nextval)
end
after insert on test_goods --插入之后触发
--after UPDATE ON test_goods --更新之后触发
FOR EACH ROW
DECLARE
-- 当 GOODS_NUM 的值<CAUTION_NUM 的值的时候,我就向名为INDENT的表中插入
--GOODS_ID,GOODS_NUM,GOODS_NAME,INDENT_DATE,INDENT_NUM
--其中INDENT_NUM为创建的序列IDENTSEQUENCE生成的序列的值。
--AUTHOR 戴忠德
--LOCAL VARIABLES HERE
BEGIN
IF :OLD.GOODS_NUM < :OLD.CAUTION_NUM then
--向名为INDENT的表中插入
--DBMS_OUTPUT.PUT_LINE('GOODS_NUM 的值<CAUTION_NUM 准备插入,开始执行触发器.....');
INSERT INTO TEST_INDENT(GOODS_ID, GOODS_NUM, GOODS_NAME, INDENT_NUM)
values (:OLD.GOODS_ID, :OLD.GOODS_NUM, :OLD.GOODS_NAME, IDENTSEQUENCE.nextval) ; --DBMS_OUTPUT.PUT_LINE('GOODS_NUM 的值<CAUTION_NUM ,触发器执行成功');
END IF;
END TR_T_GOODS_aiau;
CREATE TABLE "XXZWZX"."TEST_GOODS"
( "GOODS_ID" NUMBER NOT NULL ENABLE,
"GOODS_NUM" NUMBER,
"GOODS_NAME" VARCHAR2(20),
"CAUTION_NUM" NUMBER,
CONSTRAINT "PK_TEST_GOODS_GOODS_ID" PRIMARY KEY ("GOODS_ID")
)
--创建表2
CREATE TABLE "XXZWZX"."TEST_INDENT"
( "GOODS_ID" NUMBER NOT NULL ENABLE,
"GOODS_NUM" NUMBER,
"GOODS_NAME" VARCHAR2(20),
"INDENT_DATE" CHAR(19) DEFAULT to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'), --这样时间就不用插了
"INDENT_NUM" NUMBER,
CONSTRAINT "PK_TEST_INDENT_INDENT_NUM" PRIMARY KEY ("INDENT_NUM")
)
那张表有2个<CAUTION_NUM的值,update的时候又有一个,这样原先2个已经插入了,再触发那个触发器会再试图把那2个加进去,这样就会重复。
我想只有更新前>=CAUTION_NUM并且更新后<=CAUTION_NUM的表项加到那个INDENT表里面