使用DML触发器时,触发器代码不能从触发器所对应的基表中读取数据,例如基于t1表的触发器,那么触发器就不能包含对t1表的查询操作
1、BEFORE 语句触发器
Create Or Replace Trigger t_1
Before Insert Or Update Or Delete On T1
Begin
If TO_CHAR(Sysdate,DY,'nls_date_language=AMERICAN') In ('SAT','SUN')
Then
raise_application_error(-20001,'不能在休息日改变信息');
End If;
End;
2、使用谓词 INSERTING,UPDATING,DELETING
Create Or Replace Trigger T_2
Before Insert Or Update Or Delete On T1
Begin
If TO_CHAR(Sysdate,'DY','nls_date_language=AMERICAN') In ('SAT','SUN')
Then
Case
When INSERTING Then
raise_application_error(-20001,'不能在休息日添加信息');
When UPDATING Then
raise_application_error(-20001,'不能在休息日改变信息');
When DELETING Then
raise_application_error(-20001,'不能在休息日删除信息');
End Case;
End If;
End;
3、BEFORE 行触发器
Create Or Replace Trigger T_3
Before Insert Or Update Or Delete On T1
For Each Row
Begin
If :New.sal < :Old:sal Then
raise_application_error(-20001,'工资只涨不降');
End If;
End;
4、显示触发器信息
Select TRIGGER_NAME,STATUS From USER_TRIGGERS Where TABLE_NAME = 'T1';
5、禁止、激活触发器
Alter Trigger T_1 Disable;
Alter Trigger T_1 Enable;
6、禁止或激活表的所有触发器
Alter Table T1 Disable All Triggers;
Alter Table T1 Enable All Triggers;
7、删除触发器
Drop Trigger T_1;