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.
On this page of the site you can watch the video online Create a Trigger in a Table with Oracle | SQL PLSQL with a duration of hours minute second in good quality, which was uploaded by the user Cloud Research & Software Engineering 02 March 2024, share the link with friends and acquaintances, this video has already been watched 542 times on youtube and it was liked by 10 viewers. Enjoy your viewing!
