Oracle Series: (30) Flip-flop

What is a trigger [Trigger]?

For different DML (select/update/delete/insert) operations, the trigger can intercept to a certain extent, and the qualified operation can operate the base table; otherwise, the base table cannot be operated. Similar to Filter in JavaWeb and Iterceptor in Struts2.

Why use triggers?

If there is no trigger, then all DML operations can operate on the base table without restrictions, which does not meet the business requirements.

Create a statement-level trigger insertEmpTrigger, and display "hello world" before adding [insert] to the table [emp].

Create or replace trigger insertEmpTriggerbeforeinserton empbegin dbms_output.put_line ('hello world'); end;/

Delete trigger insertEmpTrigger, syntax: drop trigger trigger name

Drop trigger insertEmpTrigger

Insert a record using the insert statement, causing the insertEmpTrigger trigger to work

Insert into emp (empno,ename) values (1234 dint 'Xiao Ming')

Use insert statement to insert N records, causing insertEmpTrigger trigger to work

Insert into emp select * from xxx_emp

Create a statement-level trigger deleteEmpTrigger. After deleting the table [emp] and [delete], [after] displays "world hello".

Create or replace trigger deleteEmpTriggerbeforedeleteon empbegin dbms_output.put_line ('delete data'); end;/

Delete a record using the delete statement, causing the deleteEmpTrigger trigger to work

Delete from emp where empno=7369

Delete N records using delete statement, causing deleteEmpTrigger trigger to work

Delete from emp where 1 # 1

Monday to Friday, and 9-20:00 can insert data into the database emp table, otherwise use the function to throw an exception

Syntax: raise_application_error ('- 200000 exception reasons')

Create or replace trigger securityTriggerbeforeinserton empdeclare pday varchar2 (20); phour number (2); begin select to_char (sysdate,'day') into pday from dual; select to_char (sysdate,'hh34') into phour from dual; if pday in ('Saturday', 'Sunday') or phour not between 7 and 22 then raise_application_error

Create a row-level trigger checkSalaryTrigger to ensure that the post-rise salary column is greater than the pre-increase salary. Syntax: for each row/:new.sal/:old.sal

Create or replace trigger checkSalaryTriggerafterupdate of salon empfor each rowbegin if: new.sal