Create Trigger(建立DML/DDL触发器)

CREATE OR REPLACE TRIGGER loc_dtl_monitor
BEFORE INSERT OR UPDATE OR DELETE
ON loc_dtl
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
BEGIN
INSERT INTO loc_dtl_new(LOC,ITEM_NO,QLT,LOT_NO,VERSION,FLAG,
DATECODE,BRANCH,VENDOR,PLT_QTY,ACT_QTY,IN_DTE,IN_TIM,CHK_DTE,CHK_TIM,REMARK,GET_TIME)
values(:OLD.LOC,:OLD.ITEM_NO,:OLD.QLT,:OLD.LOT_NO,:OLD.VERSION,:OLD.FLAG,:OLD.DATECODE,:OLD.BRANCH,
:OLD.VENDOR,:OLD.PLT_QTY,:OLD.ACT_QTY,:OLD.IN_DTE,:OLD.IN_TIM,:OLD.CHK_DTE,:OLD.CHK_TIM,:OLD.REMARK,SYSDATE);
END;
/

[@more@]

CREATE OR REPLACE TRIGGER loc_dtl_monitor
BEFORE INSERT OR UPDATE OR DELETE
ON loc_dtl
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
BEGIN
INSERT INTO loc_dtl_new(LOC,ITEM_NO,QLT,LOT_NO,VERSION,FLAG,
DATECODE,BRANCH,VENDOR,PLT_QTY,ACT_QTY,IN_DTE,IN_TIM,CHK_DTE,CHK_TIM,REMARK,GET_TIME)
values(:OLD.LOC,:OLD.ITEM_NO,:OLD.QLT,:OLD.LOT_NO,:OLD.VERSION,:OLD.FLAG,:OLD.DATECODE,:OLD.BRANCH,
:OLD.VENDOR,:OLD.PLT_QTY,:OLD.ACT_QTY,:OLD.IN_DTE,:OLD.IN_TIM,:OLD.CHK_DTE,:OLD.CHK_TIM,:OLD.REMARK,SYSDATE);
END;
/

------------------------------------------------------------------------------------------
CREATE OR REPLACE TRIGGER loc_dtl_monitor
BEFORE INSERT OR UPDATE OR DELETE
ON loc_dtl
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
BEGIN
IF INSERTING OR UPDATING THEN
INSERT INTO loc_dtl_new(LOC,ITEM_NO,QLT,LOT_NO,VERSION,FLAG,
DATECODE,BRANCH,VENDOR,PLT_QTY,ACT_QTY,IN_DTE,IN_TIM,CHK_DTE,CHK_TIM,REMARK,GET_TIME)
values(:NEW.LOC,:NEW.ITEM_NO,:NEW.QLT,:NEW.LOT_NO,:NEW.VERSION,:NEW.FLAG,:NEW.DATECODE,:NEW.BRANCH,
:NEW.VENDOR,:NEW.PLT_QTY,:NEW.ACT_QTY,:NEW.IN_DTE,:NEW.IN_TIM,:NEW.CHK_DTE,:NEW.CHK_TIM,:NEW.REMARK,SYSDATE);
ELSIF DELETING THEN
INSERT INTO loc_dtl_new(LOC,ITEM_NO,QLT,LOT_NO,VERSION,FLAG,
DATECODE,BRANCH,VENDOR,PLT_QTY,ACT_QTY,IN_DTE,IN_TIM,CHK_DTE,CHK_TIM,REMARK,GET_TIME)
values(:OLD.LOC,:OLD.ITEM_NO,:OLD.QLT,:OLD.LOT_NO,:OLD.VERSION,:OLD.FLAG,:OLD.DATECODE,:OLD.BRANCH,
:OLD.VENDOR,:OLD.PLT_QTY,:OLD.ACT_QTY,:OLD.IN_DTE,:OLD.IN_TIM,:OLD.CHK_DTE,:OLD.CHK_TIM,:OLD.REMARK,SYSDATE);
END;
/

Insert into smpasrs.loc_dtl(LOC,ITEM_NO,QLT,LOT_NO,FLAG,DATECODE,BRANCH,VENDOR,PLT_QTY,ACT_QTY,IN_DTE,IN_TIM)
VALUES('AAAAAA','BBBBBB','G','A','0','20091005','d888','8888',8888,8888,'20091005',to_char(sysdate,'yyyymmdd'))

UPDATE LOC_DTL SET LOC='BBBBBB' WHERE ITEM_NO='BBBBBB';

DELETE LOC_DTL WHERE ITEM_NO='BBBBBB' AND IN_TIM='20091105';

SELECT * from LOC_DTL WHERE ITEM_NO='BBBBBB';

SELECT * from LOC_DTL_NEW

------------------------------------------
CREATE OR REPLACE TRIGGER LOC_DTL_MONITOR
BEFORE DELETE OR INSERT OR UPDATE
ON SMPASRS.LOC_DTL
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
BEGIN

IF INSERTING THEN

INSERT INTO SMPASRS.loc_dtl_new(LOC,ITEM_NO,QLT,LOT_NO,VERSION,FLAG,
DATECODE,BRANCH,VENDOR,PLT_QTY,ACT_QTY,IN_DTE,IN_TIM,CHK_DTE,CHK_TIM,REMARK,GET_TIME,ACTION)
values(:NEW.LOC,:NEW.ITEM_NO,:NEW.QLT,:NEW.LOT_NO,:NEW.VERSION,:NEW.FLAG,:NEW.DATECODE,:NEW.BRANCH,
:NEW.VENDOR,:NEW.PLT_QTY,:NEW.ACT_QTY,:NEW.IN_DTE,:NEW.IN_TIM,:NEW.CHK_DTE,:NEW.CHK_TIM,:NEW.REMARK,SYSDATE,'INSERTING');

ELSIF UPDATING THEN

INSERT INTO SMPASRS.loc_dtl_new(LOC,ITEM_NO,QLT,LOT_NO,VERSION,FLAG,
DATECODE,BRANCH,VENDOR,PLT_QTY,ACT_QTY,IN_DTE,IN_TIM,CHK_DTE,CHK_TIM,REMARK,GET_TIME,ACTION)
values(:OLD.LOC,:OLD.ITEM_NO,:OLD.QLT,:OLD.LOT_NO,:OLD.VERSION,:OLD.FLAG,:OLD.DATECODE,:OLD.BRANCH,
:OLD.VENDOR,:OLD.PLT_QTY,:OLD.ACT_QTY,:OLD.IN_DTE,:OLD.IN_TIM,:OLD.CHK_DTE,:OLD.CHK_TIM,:OLD.REMARK,SYSDATE,'UPDATING');

ELSIF DELETING THEN

INSERT INTO SMPASRS.loc_dtl_new(LOC,ITEM_NO,QLT,LOT_NO,VERSION,FLAG,
DATECODE,BRANCH,VENDOR,PLT_QTY,ACT_QTY,IN_DTE,IN_TIM,CHK_DTE,CHK_TIM,REMARK,GET_TIME,ACTION)
values(:OLD.LOC,:OLD.ITEM_NO,:OLD.QLT,:OLD.LOT_NO,:OLD.VERSION,:OLD.FLAG,:OLD.DATECODE,:OLD.BRANCH,
:OLD.VENDOR,:OLD.PLT_QTY,:OLD.ACT_QTY,:OLD.IN_DTE,:OLD.IN_TIM,:OLD.CHK_DTE,:OLD.CHK_TIM,:OLD.REMARK,SYSDATE,'DELETING');

END IF;

END;
/

---------------------------------------------------------

用触发器监控数据库的DDL操作(转)

-- Create table
create table DDL_LOG
(
USER_NAME VARCHAR2(30),
DDL_DATE DATE,
DDL_TYPE VARCHAR2(30),
OBJECT_TYPE VARCHAR2(18),
OWNER VARCHAR2(30),
OBJECT_NAME VARCHAR2(128),
IP_ADDRESS VARCHAR2(40),
OSUSER VARCHAR2(30),
TERMINAL VARCHAR2(30)
);

--Create trigger
CREATE OR REPLACE TRIGGER ddl_trigger
AFTER DDL
ON DATABASE

BEGIN
INSERT INTO ddl_log
(user_name,
ddl_date,
ddl_type,
object_type,
owner,
object_name,
ip_address,
osuser,
terminal)
VALUES
(ora_login_user,
SYSDATE,
ora_sysevent,
ora_dict_obj_type,
ora_dict_obj_owner,
ora_dict_obj_name,
SYS_CONTEXT('USERENV', 'IP_ADDRESS'),
SYS_CONTEXT('USERENV', 'OS_USER'),
SYS_CONTEXT('USERENV', 'TERMINAL'));
END ddl_trigger;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/789833/viewspace-1029241/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/789833/viewspace-1029241/