创建一个记录dml的触发器

创建两个表.其实test为我们在操作的表.而script为记录对test表进行dml操作的script:

SQL> create table SCRIPT
2 (
3 SCRIPT VARCHAR2(4000),
4 TABLENAME VARCHAR2(20),
5 RUNDATE DATE
6 )
7 /

Table created

SQL>
SQL> create table TEST
2 (
3 ID NUMBER not null,
4 NAME VARCHAR2(20)
5 )
6 /

Table created

--创建触发器

SQL> Create Or Replace Trigger Trg_Test
2 After Insert Or Update Or Delete On Test
3 For Each Row
4 Declare
5 l_Dmlsql Varchar2(4000);
6 Begin
7 If Inserting Then
8 l_Dmlsql := 'insert into test(id,name) values(' || :New.Id || ',' || :New.Name || ');';
9 Elsif Updating Then
10 l_Dmlsql := 'UPDATE TEST SET name=' || :New.Name || ' where id=' || :Old.Id || ' ;';
11 Elsif Deleting Then
12 l_Dmlsql := 'DELETE test WHERE id=' || :Old.Id || ' ;';
13 End If;
14 Insert Into script (script, tablename, rundate) Values (l_Dmlsql, 'TEST', Sysdate);
15 End;
16 /

Trigger created

SQL>

--测试

SQL> insert into test values(1,'aa');

1 row inserted

SQL> commit;

Commit complete

SQL> insert into test values(2,'bb');

1 row inserted

SQL> update test set name='xy' where id=2;

1 row updated

SQL> delete test;

2 rows deleted

SQL> commit;

Commit complete

SQL> select * from script;

SCRIPT TABLENAME RUNDATE
-------------------------------------------------------------------------------- -------------------- -----------
insert into test(id,name) values(1,aa); TEST 2006-6-15 1
insert into test(id,name) values(2,bb); TEST 2006-6-15 1
UPDATE TEST SET name=xy where id=2 ; TEST 2006-6-15 1
DELETE test WHERE id=1 ; TEST 2006-6-15 1
DELETE test WHERE id=2 ; TEST 2006-6-15 1

SQL>

注意:这里没有对具体的数据类型作处理.可以另写函数实现.

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

转载于:http://blog.itpub.net/104152/viewspace-139932/