做一个表的insert的trigger,目的是只修改插入行的字段。
CREATE
OR
REPLACE
TRIGGER
TR_RME_SLOT
BEFORE
INSERT
ON
RME_SLOT
FOR
EACH
ROW
BEGIN
IF
(:NEW.
POSITION
>=
0
AND
:NEW.
POSITION
<
10
)
THEN
:NEW.
SLOT_NAME
:=
'0'
||TO_CHAR
(:NEW.
POSITION
);
ELSE
:NEW.
SLOT_NAME
:=
TO_CHAR
(:NEW.
POSITION
);
END
IF;
END;
在插入以前就需要修改插入行;在trigger实现中并不需要用到update语句
同时,如果要在trigger中实现对本表记录的修改,则需要这样写:
CREATE
OR
REPLACE
TRIGGER
TR_RME_SLOT
AFTER
INSERT
ON
RME_SLOT
FOR
EACH
ROW
DECLARE
PRAGMA
AUTONOMOUS_TRANSACTION
;
BEGIN
IF
(:NEW.
POSITION
>=
0
AND
:NEW.
POSITION
<
10
)
THEN
UPDATE
RME_SLOT
SET
SLOT_NAME
=
'0'
||TO_CHAR
(:NEW.
POSITION
)
WHERE
SLOT_ID
=:NEW.
SLOT_ID
;
ELSE
UPDATE
RME_SLOT
SET
SLOT_NAME
=
TO_CHAR
(:NEW.
POSITION
)
WHERE
SLOT_ID
=:NEW.
SLOT_ID
;
END
IF;
COMMIT;
END;
注意多了一段DECLARE,同时在trigger结束时需要COMMIT
本人经过一天的苦战,修改后成功执行一触发器:
create or replace trigger tri_ZSY_LEAVETABLE
before insert ON "ZSY_LEAVETABLE" FOR EACH ROW
before insert ON "ZSY_LEAVETABLE" FOR EACH ROW
declare cctype number ;
begin
select posi_type into cctype from base_gov.a_frameuser
where user_code=:new.personname;
:new.persontype:=cctype;
end;
begin
select posi_type into cctype from base_gov.a_frameuser
where user_code=:new.personname;
:new.persontype:=cctype;
end;