column_triger.txt

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

desc user_triggers;
select trigger_body from user_triggers;


--列级触发器
--如果UPDATE时间不在'08' AND '10'，报错！
--检验，如果不UPDATE SAL,PUDATE其它字段，会如何？
CREATE OR REPLACE TRIGGER secure_emp
before UPDATE OF SAL ON emp
--BEFORE INSERT ON emp
  BEGIN
	  --如果不在'08' AND '10'时间
	  IF (TO_CHAR (sysdate,'DY') IN ('SAT','SUN'))  OR (TO_CHAR(sysdate,'HH24')) NOT BETWEEN  '08' AND '10' 
	  THEN
	   RAISE_APPLICATION_ERROR (-20500, 'You may only insert into EMP during normal hours.');
	  END IF;
  END;

--INSERT INTO 如果不在上班时间，TRIGGER报错！
INSERT INTO emp (empno, ename, deptno)
VALUES          (7778, 'BAUWENS', 20);
--OK
update emp set sal=10001  where empno=5;
--TRIGGER STOP THE UPDATE

update emp set ename='shi'  where empno=5;
--trigger 未触发。列级触发器，只在对相应列做dml时才触发。



----------
DML Trigger Example with Restriction
This example creates a BEFORE row trigger named SALARY_CHECK in the schema SCOTT. The PL/SQL block might specify, for example, that the employee's salary must fall within the established salary range for the employee's job: 

CREATE TRIGGER scott.salary_check 
    BEFORE 
    INSERT OR UPDATE OF sal, job ON scott.emp 
    FOR EACH ROW 
    WHEN (new.job <> 'PRESIDENT') 
    BEGIN
	--在trigger中以dept表的更改值更改emp（约束表）
 	UPDATE emp
 	SET    emp.deptno = :new.deptno
	WHERE  emp.deptno = :old.deptno;
   END;
--注意：以下为oracle对列级触发器的说明。      
Oracle fires this trigger whenever one of the following statements is issued: 

an INSERT statement that adds rows to the EMP table 

an UPDATE statement that changes values of the SAL or JOB columns of the EMP table 


raise_application_error


------------------------------------------
CREATE TRIGGER scott.salary_check
   BEFORE INSERT OR UPDATE OF sal, job ON scott.emp
   FOR EACH ROW
   WHEN (new.job<> 'PRESIDENT')
   CALL check_sal(:new.job, :new.sal, :new.ename);