trigger_row_sm.txt

orale培训教材包括了所有的sql说明和实例

--CREATE TABLE :audit_emp_values
CREATE TABLE audit_sm_emp
(user_name VARCHAR2(50),
timestamp  DATE, 
id varchar(20),
old_name VARCHAR2(10), 
new_name VARCHAR2(10),
old_salary NUMBER(7,2), 
new_salary NUMBER(7,2),
old_tel varchar2(10),
new_tel varchar2(10));

--CREATE TRIGGER USE :old   and   :new
CREATE OR REPLACE TRIGGER audit_sm_values
AFTER DELETE OR INSERT OR UPDATE ON sm_emp
FOR EACH ROW
 BEGIN
    INSERT INTO audit_sm_emp (user_name,
     timestamp, id, old_name, new_name,
      old_salary, new_salary,old_tel,new_tel)
    VALUES (USER, SYSDATE, :old.empid, :old.name,
   :new.name, :old.salary, :new.salary,:old.telno,:new.telno);
 END;


--test :insert into emp
--DISABLE THE TRIGGER: SCOTT.SECURE_EMP THEN INSERT.
ALTER TRIGGER SCOTT.SECURE_EMP DISABLE;
INSERT INTO emp VALUES (999,'TOM999','DEVELOPER',9,NULL,9999.99,9,10);
--IF OK ,SELECT 
SELECT * FROM audit_emp_values;


--DISABLE A TRIGGER
ALTER TRIGGER SCOTT.SECURE_EMP DISABLE;
ALTER TABLE emp   DISABLE ALL TRIGGERS;
--TEST
 INSERT INTO emp VALUES (998,'TOM999','DEVELOPER',9,NULL,9999.99,9,10);
ALTER TABLE emp   ENABLE  ALL TRIGGERS;
--TEST
 INSERT INTO emp VALUES (998,'TOM999','DEVELOPER',9,NULL,9999.99,9,10);

--DEL DATA INSERTED INTO FOR THE NEXT TEST
DELETE FROM emp WHERE empno IN (999,998,997);



--change data in a constraint table
--create constraint
ALTER TABLE dept ADD CONSTRAINT fk_emp FOREIGN KEY(deptno) REFERENCES emp(deptno);

--create trigger
CREATE OR REPLACE TRIGGER cascade_updates
  AFTER UPDATE OF deptno on DEPT
  FOR EACH ROW
   BEGIN
 	UPDATE emp
 	SET    emp.deptno = :new.deptno
	WHERE  emp.deptno = :old.deptno;
   END;
--update test
UPDATE dept SET    deptno = 1
WHERE  deptno = 30;





--注意危险！：
--当emp中没有deptno = 30的数据，此update成功。
--如果有deptno = 30的数据，则不成功。
INSERT INTO emp VALUES (998,'TOM999','SALES',9,NULL,9999.99,9,30);