Triggers

Database trigger is a PL/SQL block that is executed on an event in the database. The event is raised to particular data manipulation of a table such as inserting, updating or deleting a row of a table.

Triggers may be used for any of the following:

To implement complex business rules which cannot be implemented by integrity constraints.

To audit the process, i.e, keep track of changes made to a table.

To automatically perform an action when another concerned action takes place.

For example, updating a table whenever there is an insertion of a row into another table.

Types Of Triggers
Triggers are classified into different categories based on whether they are executed on row or statement, where they are fired before or after the event.

Row-level Trigger
A row-level trigger is fired once for each row that is affected by DML command. 

For example, if an update command updates 100 rows then row-level trigger is fired 100 times for each row.

Statement-level Trigger

A statement trigger is fired only for once for a DML statement irrespective of the number of rows affected by the statement.

Before Triggers

While defining a trigger, you can specify whether the trigger is to be fired before the command(INSERT,UPDATE and DELETE) is executed or after the command is executed.

After Trigger
After triggers are fired after the triggering action is completed. For example, if after trigger is associated with INSERT command then it is fired after the row is inserted into the table.

Note: To run Trigger programs ,the best way is to use the default DBA i.e 

SQL> conn hr/hr

Connected.

How to Create a Database Trigger using row-level and statement level triggers

SQL> desc employees

 Name                                      Null?    Type

 ---------------------------------- -------- ----------------------------

 EMPLOYEE_ID                       NOT NULL       NUMBER(6)

 FIRST_NAME                                              VARCHAR2(20)

 LAST_NAME                          NOT NULL      VARCHAR2(25)

 EMAIL                                   NOT NULL     VARCHAR2(25)

 PHONE_NUMBER                                       VARCHAR2(20)

 HIRE_DATE                            NOT NULL     DATE

 JOB_ID                                  NOT NULL    VARCHAR2(10)

 SALARY                                                     NUMBER(8,2)

 COMMISSION_PCT                                     NUMBER(2,2)

 MANAGER_ID                                            NUMBER(6)

 DEPARTMENT_ID                                       NUMBER(4)

SQL> create table emp55 as select employee_id,first_name,hire_date,salary from employees;

Table created.

SQL> desc emp55;

 Name                                      Null?    Type

 ---------------------------------- -------- ----------------------------

 EMPLOYEE_ID                                               NUMBER(6)

 FIRST_NAME                                                 VARCHAR2(20)

 HIRE_DATE                                 NOT NULL    DATE

 SALARY                                                         NUMBER(8,2)

Ex: Row-level Trigger
/*Write a ROW-level trigger to check hire_date column of employees table during insertion and updating. If hire_date is greater than sysdate trigger raises application error.*/

create or replace trigger emp55_hiredate

before insert or update of hire_date on emp55

for each row

begin

if :new.hire_date>sysdate then

raise_application_error(-20200,'Hire date cannot be after sysdate');

end if;

end;

/

Output:

SQL> @d:\rl.sql;

Trigger created.

Check your trigger working or not:

SQL> update emp55 set hire_date='6-jul-2022';

update emp55 set hire_date='6-jul-2022'

       *

ERROR at line 1:

ORA-20200: Hire date cannot be after sysdate

ORA-06512: at "HR.EMP55_HIREDATE", line 3

ORA-04088: error during execution of trigger 'HR.EMP55_HIREDATE'

Ex: Statement-level Trigger
Sunday-1,Monday-2 ,Tuesday-3,wednesday-4,thursday-5,friday-6,saturday-7

SQL> select sysdate from dual;
SYSDATE
---------
02-NOV-21
SQL> select to_char(sysdate,'day') from dual;
TO_CHAR(SYSDATE,'DAY')
------------------------------------
tuesday
SQL> select to_char(sysdate,'d') from dual;
T
-
3

/*Write a Statement-level trigger that prevents any changes made to emp55 table on Tuesday.*/ 

create or replace trigger emp55_tue

before insert or update or delete on emp55

begin

if to_char(sysdate,'d')=3 then

raise_application_error(-20210,'No changes can be made on tuesday');

end if;

end;

/

Ouputs:

SQL> @d:\rl.sql

Trigger created.

SQL> delete from emp55;

delete from emp55

            *

ERROR at line 1:

ORA-20210: No changes can be made on tuesday

ORA-06512: at "HR.EMP55_TUE", line 3

ORA-04088: error during execution of trigger 'HR.EMP55_TUE'

SQL> insert into emp55(hire_date) values('16-jul-2022');

insert into emp55(hire_date) values('16-jul-2022')

            *

ERROR at line 1:

ORA-20210: No changes can be made on tuesday

ORA-06512: at "HR.EMP55_TUE", line 3

ORA-04088: error during execution of trigger 'HR.EMP55_TUE'

SQL> update emp55 set salary=32323;

update emp55 set salary=32323

       *

ERROR at line 1:

ORA-20210: No changes can be made on tuesday

ORA-06512: at "HR.EMP55_TUE", line 3

ORA-04088: error during execution of trigger 'HR.EMP55_TUE'