ename varchar2(50),
sal number(16,2),
deptno number(2)
)
insert into tr_emp2
selectempno,ename,sal,deptno
from emp
CREATE OR REPLACE TRIGGER Log_salary_increase
AFTER UPDATE ON tr_emp2
FOR EACH ROW
WHEN (new.Sal> 1000)
BEGIN
INSERT INTO tr_Emp_log(Emp_id,Log_date, New_salary, Action)
VALUES(:new.Empno, SYSDATE, :new.SAL, 'NEW SAL');
END;
UPDATE tr_emp2 SET Sal = Sal + 1000.0
WHERE Deptno =20;
select * from tr_Emp_log
This trigger is fired 5 times.
drop TRIGGERLog_salary_increase;
CREATE OR REPLACE TRIGGER Log_emp_update
AFTER UPDATE ON tr_emp2
BEGIN
INSERT INTO tr_Emp_log(Log_date, Action)
VALUES (SYSDATE, 'emp sal changed');
END;
UPDATE tr_emp2 SET Sal = Sal + 1000.0
WHERE Deptno =20;
select * from tr_Emp_log
drop TRIGGER Log_emp_update;
EMP_ID
LOG_DATE
NEW_SALARY
ACTION
7369
2008-08-28 16:36:23
1800.00
NEW SAL
7566
2008-08-28 16:36:23
3975.00
NEW SAL
7788
2008-08-28 16:36:23
4000.00
NEW SAL
7902
2008-08-28 16:36:23
4000.00
NEW SAL
7876
2008-08-28 16:36:23
2100.00
NEW SAL
2008-08-28 16:41:33
emp sal changed
WHEN
Droptable emp_trigger purge;
create table emp_trigger
as
select * from emp
CREATE OR REPLACE TRIGGERPrint_salary_changes
BEFORE DELETE OR INSERT OR UPDATE ON emp_trigger
FOR EACH ROW
WHEN (new.EMPNO*10 > 50000)
DECLARE
sal_diff number;
BEGIN
sal_diff := :NEW.SAL - :OLD.SAL;
dbms_output.put('Old salary: ' || :old.sal);
dbms_output.put(' New salary: ' || :new.sal);
dbms_output.put_line(' Difference ' || sal_diff);
END;
insert intoemp_trigger(empno,sal) values(6000,20);
Old salary: New salary: 20 Difference
CREATE OR REPLACE TRIGGERPrint_salary_changes
BEFORE DELETE OR INSERT OR UPDATE ON emp_trigger
FOR EACH ROW
WHEN (sqrt(new.EMPNO) >70.7)
DECLARE
sal_diff number;
BEGIN
sal_diff := :NEW.SAL - :OLD.SAL;
dbms_output.put('Old salary: ' || :old.sal);
dbms_output.put(' New salary: ' || :new.sal);
dbms_output.put_line(' Difference ' || sal_diff);
END;
insert intoemp_trigger(empno,sal) values(6000,20);
Old salary: New salary: 20 Difference
create or replace function func_test(p1 in integer) return integer is
Result integer;
begin
return(p1*10);
end func_test;
CREATE OR REPLACE TRIGGERPrint_salary_changes
BEFORE DELETE OR INSERT OR UPDATE ON emp_trigger
FOR EACH ROW
WHEN func_test(new.EMPNO > 50000)
DECLARE
sal_diff number;
BEGIN
sal_diff := :NEW.SAL - :OLD.SAL;
dbms_output.put('Old salary: ' || :old.sal);
dbms_output.put(' New salary: ' || :new.sal);
dbms_output.put_line(' Difference ' || sal_diff);
END;
第4 行出现错误:
ORA-04076: 无效的NEW 或OLD 说明
create or replace function func_test(p1 in integer) return integer
deterministic
is
Result integer;
begin
return(p1*10);
endfunc_test;
CREATE OR REPLACE TRIGGERPrint_salary_changes
BEFORE DELETE OR INSERT OR UPDATE ON emp_trigger
FOR EACH ROW
WHEN (func_test(new.EMPNO) >70.7)
DECLARE
sal_diff number;
BEGIN
sal_diff := :NEW.SAL - :OLD.SAL;
dbms_output.put('Old salary: ' || :old.sal);
dbms_output.put(' New salary: ' || :new.sal);
dbms_output.put_line(' Difference ' || sal_diff);
END;
依然出现错误
第4 行出现错误:
ORA-04076: 无效的NEW 或OLD 说明
WHEN(new.Parts_on_hand 复合trigger(compound trigger) (11g new feature) CREATE TABLE tr_Emp_log ( Emp_id NUMBER, Log_date DATE, New_sal