Create a Trigger in a Table with Oracle | SQL PLSQL
In Oracle SQL, a trigger is a set of instructions that are automatically executed ("triggered") in response to specific events on a particular table or view. Triggers can be used to enforce business rules, maintain data integrity, and automate complex database operations. Table triggers are associated with a specific table and can be defined to fire before or after specific events like INSERT, UPDATE, DELETE, or even on DDL (Data Definition Language) events.
Basic Syntax:
```sql
CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
[FOR EACH ROW]
DECLARE
-- Declarations (optional)
BEGIN
-- Trigger body (SQL and PL/SQL statements)
END trigger_name;
/
```
*CREATE [OR REPLACE] TRIGGER:* Defines a new trigger or replaces an existing one.
*BEFORE | AFTER:* Specifies whether the trigger fires before or after the triggering event.
*INSERT | UPDATE | DELETE:* Specifies the triggering event.
*ON table_name:* Specifies the table associated with the trigger.
*FOR EACH ROW:* Indicates that the trigger is a row-level trigger. It is optional and used when you need to reference the affected row.
Trigger Types:
1. *BEFORE Triggers:*
Executed before the triggering event.
Can be used to validate or modify data before it is changed in the table.
```sql
CREATE OR REPLACE TRIGGER before_insert_trigger
BEFORE INSERT
ON employees
FOR EACH ROW
BEGIN
-- Trigger body (example: setting a default value)
:NEW.creation_date := SYSDATE;
END before_insert_trigger;
/
```
2. *AFTER Triggers:*
Executed after the triggering event.
Can be used to perform actions after the data has been changed.
```sql
CREATE OR REPLACE TRIGGER after_update_trigger
AFTER UPDATE
ON employees
FOR EACH ROW
BEGIN
-- Trigger body (example: logging the update)
INSERT INTO audit_log (table_name, action, timestamp)
VALUES ('employees', 'UPDATE', SYSTIMESTAMP);
END after_update_trigger;
/
```
Special Trigger Variables:
*:OLD and :NEW:*
Used in row-level triggers to reference the old (pre-change) and new (post-change) values of the affected rows.
```sql
CREATE OR REPLACE TRIGGER before_update_trigger
BEFORE UPDATE
ON employees
FOR EACH ROW
BEGIN
-- Trigger body (example: prevent salary decrease)
IF :NEW.salary :OLD.salary THEN
:NEW.salary := :OLD.salary; -- Set it back to the old value
END IF;
END before_update_trigger;
/
```
Drop a Trigger:
```sql
DROP TRIGGER trigger_name;
```
Triggers are powerful database components, but they should be used judiciously to avoid unintended consequences. They are commonly used for enforcing complex business rules, maintaining audit logs, and automating data-related tasks.