create or repalce trigger helpckn126 after update on tablename of name for eache row as declare begin update tablename set time = sysdate where name=:new.name; commit; end;
create trigger trg before update of name on a for each row begin :new.time:=sysdate; end;
create or repalce trigger helpckn126 after update on tablename of name --应该是 of name on tablename for eache row as /declare --没有as 又declare这样的声明 begin update tablename set time = sysdate where name=:new.name; --after触发器不能对编译表进行更改 commit; --触发器中不能有commit/rollback 除非是自治事物 end;
你这个会引发变异表 --给个例子参考下 create or replace trigger testtri before update of ename on emp for each row begin :new.hiredate:=sysdate; end; SQL> update emp set ename='test' where ename='SCOTT';已更新 1 行。SQL> COMMIT;提交完成。SQL> SELECT * FROM EMP WHERE ENAME='test'; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- 7788 test ANALYST 7566 30-11月-10 3000 900 20
SQL> create or replace trigger testtri 2 before update on emp --把of ename 去掉 3 for each row 4 begin 5 :new.hiredate:=sysdate; 6 end; 7 /触发器已创建SQL> update emp set sal=sal*10 where sal<2000;已更新8行。SQL> commit;提交完成。SQL> select * from emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 30-11月-10 8000 900 20 7499 ALLEN SALESMAN 7698 30-11月-10 16000 300 30 7521 WARD SALESMAN 7698 30-11月-10 12500 500 30 7566 JONES MANAGER 7839 02-4月 -81 2975 900 20 7654 MARTIN SALESMAN 7698 30-11月-10 12500 1400 30 7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 7782 CLARK MANAGER 7839 09-6月 -81 2450 10 7788 SCOTT ANALYST 7566 30-11月-10 3000 900 20 7839 KING PRESIDENT 17-11月-81 5000 10 7844 TURNER SALESMAN 7698 30-11月-10 15000 0 30 7876 ADAMS CLERK 7788 30-11月-10 11000 900 20 7900 JAMES CLERK 7698 30-11月-10 9500 30 7902 FORD ANALYST 7566 03-12月-81 3000 900 20 7934 MILLER CLERK 7782 30-11月-10 13000 10已选择14行。
after update on tablename of name
for eache row
as
declare
begin
update tablename set time = sysdate where name=:new.name;
commit;
end;
create trigger trg
before update of name on a
for each row
begin
:new.time:=sysdate;
end;
create or repalce trigger helpckn126
after update on tablename of name --应该是 of name on tablename
for eache row
as /declare --没有as 又declare这样的声明
begin
update tablename set time = sysdate where name=:new.name; --after触发器不能对编译表进行更改
commit; --触发器中不能有commit/rollback 除非是自治事物
end;
--给个例子参考下
create or replace trigger testtri
before update of ename on emp
for each row
begin
:new.hiredate:=sysdate;
end;
SQL> update emp set ename='test' where ename='SCOTT';已更新 1 行。SQL> COMMIT;提交完成。SQL> SELECT * FROM EMP WHERE ENAME='test'; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7788 test ANALYST 7566 30-11月-10 3000 900 20
2 before update on emp --把of ename 去掉
3 for each row
4 begin
5 :new.hiredate:=sysdate;
6 end;
7 /触发器已创建SQL> update emp set sal=sal*10 where sal<2000;已更新8行。SQL> commit;提交完成。SQL> select * from emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 30-11月-10 8000 900 20
7499 ALLEN SALESMAN 7698 30-11月-10 16000 300 30
7521 WARD SALESMAN 7698 30-11月-10 12500 500 30
7566 JONES MANAGER 7839 02-4月 -81 2975 900 20
7654 MARTIN SALESMAN 7698 30-11月-10 12500 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7788 SCOTT ANALYST 7566 30-11月-10 3000 900 20
7839 KING PRESIDENT 17-11月-81 5000 10
7844 TURNER SALESMAN 7698 30-11月-10 15000 0 30
7876 ADAMS CLERK 7788 30-11月-10 11000 900 20
7900 JAMES CLERK 7698 30-11月-10 9500 30
7902 FORD ANALYST 7566 03-12月-81 3000 900 20
7934 MILLER CLERK 7782 30-11月-10 13000 10已选择14行。