Create Triggers that are invoked by DML operationsPosted by tagicon_admin | in : PL/SQL
What are Triggers?
Triggers are similar to stored procedures. A trigger is a PL/SQL block that is stored in the database. A trigger is automatically executes by the Oracle database when predefined conditions occur. Basically trigger is fired or executed to a specified event. Triggers can be assigned to tables, view, schema or database.
Trigger have two level, Statement level triggers and Row level triggers. Statement level triggers is default and use the FOR EACH ROW clause when creating row level trigger. We can write triggers that to be executed in response one of the following operations occurs in the database:
–> A database manipulation (DML) statement (DELETE, INSERT, UPDATE).
–> A database definition (DDL) statement (CREATE, ALTER, DROP).
–> A database operation such as SERVERERROR, LOGON, LOGOFF, STARTUP, SHUTDOWN.
–> Simple DML triggers (INSER, UPDATE [OF column], DELETE are the possible events for DML)
- INSTEAD OF
–> Compound triggers
–> Non-DML triggers
- DDL event triggers
- Database event triggers
Syntaxt of Creating DML Trigger:
CREATE [OR REPLACE] TRIGGER trigger_name
timing — when to fire the trigger
event1[OR event2 OR event3]
[REFERENCING OLD as old / NEW as new]
FOR EACH ROW — default is statement level trigger
——-trigger body —- executable statements
**timing = BEFORE / AFTER / INSTEAD OF
**event = INSERT / DELETE / UPDATE / UPDATE OF column_list
Now I am showing an example of DML statement trigger:
CREATE OR REPLACE TRIGGER table_secure BEFORE INSERT ON employees BEGIN IF (TO_CHAR (SYSDATE, 'DY') IN ('TUE', 'WED')) OR (TO_CHAR (SYSDATE, 'HH:MI') NOT BETWEEN '08:00' AND '05:00') THEN RAISE_APPLICATION_ERROR (-20500, 'You need normal business hours to change table'); END IF; END; /
I have created a trigger to secure my EMPLOYEES table data. My trigger name is table_secure. This trigger will prevent the INSERT operation from succeeding if the business condition is violated. If a user want to insert a row into the EMPLOYEES table on Tuesday OR Wednesday or time not between 8 am to 5 pm then user will see an error message. I have used an error message manually within the RAISE_APPLICATION_ERROR.
Now I am INSERTING below data to test my trigger table_secure:
INSERT INTO employees(employee_id, last_name, email, hire_date, job_id, salary) VALUES(420, 'Jordan', 'EMJORDAN', SYSDATE, 'IT_PROG', 50000);
After execute the above query, I am getting the following error messages:
INSERT INTO employees(employee_id, last_name, email, hire_date, job_id, salary) * ERROR at line 1: ORA-20500: You need normal business hours to change table ORA-06512: at "HR.TABLE_SECURE", line 4 ORA-04088: error during execution of trigger 'HR.TABLE_SECURE'
SO, I need normal business hour to insert this data into the table.
–> Example of row level trigger:
CREATE OR REPLACE TRIGGER restrict_salary BEFORE INSERT OR UPDATE OF salary ON employees FOR EACH ROW BEGIN IF NOT :NEW.salary > 15000 THEN RAISE_APPLICATION_ERROR(-20300, 'Employee salary is less then $15000.'); END IF; END; /
My above trigger is created to restrict salary for each row. I have given a condition to INSERT or UPDATE for salary. If new salary is greater than 15000 then it will be UPDATE or INSERTED otherwise oracle database will show an error message.
Now I am using below data to test my trigger.
UPDATE employees SET salary = 14500 WHERE last_name = 'Ozer';
After executing the above query , I am getting the following exception error messages :
UPDATE employees * ERROR at line 1: ORA-20300: Employee salary is less then $15000. ORA-06512: at "HR.RESTRICT_SALARY", line 3 ORA-04088: error during execution of trigger 'HR.RESTRICT_SALARY'
So,we need salary amount more then 15000 for UPDATE data.