Oct 1112 min read

A Comprehensive Guide to SQL Triggers: DML Triggers - PART I

In SQL, triggers are special database objects that automatically perform specified actions when particular database events occur. These events include data manipulation (like INSERT, UPDATE, or DELETE) or schema modifications. Triggers are widely used to maintain data integrity, enforce business rules, log changes, or automate tasks that would otherwise require manual coding in application logic.

Why Use SQL Triggers?

Triggers are especially useful for:

Enforcing validation rules: Ensuring data integrity by validating data before it is committed.
Automating workflows: Automatically updating related tables or performing complex actions without additional application logic.
Auditing changes to sensitive data: Keeping track of modifications made to crucial data points.
Implementing complex business logic: Ensuring that the database enforces rules consistently.

Understanding SQL Triggers: Structure and Hierarchy

In this blog, we'll explore the structure and hierarchy of SQL triggers, looking at their types, execution levels, and timing. We'll also see detailed examples of each type of trigger to help you understand how to apply them in real-world scenarios.

SQL Trigger Structure

Trigger Types:
- DML (Data Manipulation Language) Triggers: Fired by INSERT, UPDATE, or DELETE operations on tables; the most commonly used type.
- DDL (Data Definition Language) Triggers: Fired by schema-altering commands like CREATE, ALTER, or DROP; helps monitor and log schema changes.
- Logon Triggers: Executed in response to user logins; used to enforce access policies or log session details.
Trigger Levels:
- Row-Level Triggers: Fired once for each row affected by the triggering SQL statement, allowing for granular control.
- Statement-Level Triggers: Fired once for the entire SQL statement, regardless of how many rows are affected; often used for auditing.
Trigger Timings:
- Before Triggers: Executed before the operation, allowing for data validation or modifications.
- After Triggers: Executed after the operation, typically for logging or performing actions post-modification.
- Instead Of Triggers: Primarily used with views; allow you to specify custom actions instead of the default behavior.

Hierarchy of SQL Triggers

To visualize the structure, let’s map out the hierarchical order:

Step 1: Trigger Types
- DML Triggers
- DDL Triggers
- Logon Triggers
Step 2: Levels of Triggers
- DML Triggers: Can have Row-Level and Statement-Level Triggers.
- DDL Triggers: Usually Statement-Level (affecting the schema).
- Logon Triggers: Operate like Statement-Level Triggers.
Step 3: Timing for Each Level
- Before, After, Instead Of (mainly for views in DML triggers).

1. DML Trigger for INSERT :

Row-Level Trigger

Before Trigger : Validates data or generate logs before insertion.

Step 1: Create a Function

You need to define a trigger function that will contain the logic to execute when the trigger is fired. For example:

CREATE OR REPLACE FUNCTION log_before_insert_film() 
RETURNS TRIGGER AS $$ 
BEGIN      
RAISE NOTICE 'Inserting film with ID: %', NEW.film_id; 
RETURN NEW; -- Allow insertion to proceed 
END; 
$$ LANGUAGE plpgsql;

Step 2: Delete Any Old Trigger if Existing

Before creating a new trigger, it’s a good practice to drop any existing trigger of the same name to avoid conflicts:

DROP TRIGGER IF EXISTS before_insert_film_trigger ON film;

Step 3: Create New Trigger

Now, you create a new trigger that will call the function you just defined whenever an insert operation occurs on the film table:

CREATE TRIGGER before_insert_film_trigger 
BEFORE INSERT ON film 
FOR EACH ROW  
EXECUTE FUNCTION log_before_insert_film();

Step 4: Try to Insert Values

You can now insert a new row into the film table to see the trigger in action:

INSERT INTO film VALUES (61081, 'Hello Barak', 'Battle Between Two Kings', 2010, 1, 3, 4.99, 150, 15.23, 'R', CURRENT_TIMESTAMP, '{Trailor}', 'chamber:1');

Step 5: See the RAISE NOTICE Messages Before the Insert

After executing the insert statement, you should see the raised notice message in your console, confirming the ID of the film being inserted:

NOTICE:  Inserting film with ID: 61081

Row-Level Trigger

After Trigger : Validates data or logs completion after insertion.

Step 1: Create a Function

First, you define a trigger function that will log a message with the film ID whenever a row is inserted into the film table. This function is called for each row being inserted:

CREATE OR REPLACE FUNCTION log_after_insert_film() 
RETURNS TRIGGER AS $$ 
BEGIN      
RAISE NOTICE 'Inserted film with ID: %', NEW.film_id; 
RETURN NEW; -- Allow insertion to proceed 
END; $$ LANGUAGE plpgsql;

Step 2: Delete Any Old Trigger if Existing

Before creating a new trigger, it's important to remove any existing trigger with the same name to prevent conflicts:

DROP TRIGGER IF EXISTS after_insert_film_trigger ON film;

Step 3: Create New Trigger

Now, you create a new trigger that will call the function you just defined every time a new row is inserted into the film table:

CREATE TRIGGER after_insert_film_trigger 
AFTER INSERT ON film 
FOR EACH ROW  
EXECUTE FUNCTION log_after_insert_film();

Step 4: Try to Insert Values

You can now insert a new row into the film table to see the trigger in action:

INSERT INTO film VALUES  (61081, 'Hello Barak', 'Battle Between Two Kings', 2010, 1, 3, 4.99, 150, 15.23, 'R', CURRENT_TIMESTAMP, '{Trailor}', 'chamber:1');

Step 5: See the RAISE NOTICE Messages After the Insert

After executing the insert statement, you should see the raised notice message in your console, confirming the ID of the film that was just inserted:

NOTICE:  Inserted film with ID: 61081

Statement-Level Trigger

Before Trigger: Validates or Logs an action to the entire insert statement before insertion.

Step 1: Create a Function

First, you define a trigger function that will log a message whenever an insert statement is executed on the film table. This function is executed once for the entire statement, not for each row:

CREATE OR REPLACE FUNCTION before_insert_film_statement_trigger() RETURNS TRIGGER AS $$ 
BEGIN      
RAISE NOTICE 'A new batch of films is being inserted into the film table'; RETURN NEW; -- Proceed with insertion 
END; $$ LANGUAGE plpgsql;

Step 2: Create the Trigger

Next, you create a trigger (drop the trigger if exists already) that will call the above function before any insert operation on the film table. This trigger is defined to fire before the entire insert statement:

DROP TRIGGER IF EXISTS before_insert_film_statement ON film;

CREATE TRIGGER before_insert_film_statement 
BEFORE INSERT ON film 
FOR EACH STATEMENT EXECUTE FUNCTION before_insert_film_statement_trigger();

Step 3: Insert Values

You can now perform an insert operation that includes multiple rows at once. The trigger will activate, logging the action for the entire insert statement:

INSERT INTO film VALUES  
(61094, 'Movie Title 1', 'Description 1', 2021, 1, 3, 4.99, 120, 7.99, 'PG-13', CURRENT_TIMESTAMP, '{Trailers}', 'Sample Text 1'), 
(61095, 'Movie Title 2', 'Description 2', 2022, 1, 5, 5.99, 130, 5.89, 'PG', CURRENT_TIMESTAMP, '{Trailers}', 'Sample Text 2');

Step 4: See the RAISE NOTICE Message

After executing the insert statement, you should see the raised notice message in your console, indicating that a batch of films is being inserted:

NOTICE:  A new batch of films is being inserted into the film table

Statement-Level Trigger

After Trigger: Validates or Logs an action to the entire insert statement after insertion.

Step 1: Create a Function

First, you define a trigger function that will log a message after an insert statement is executed on the film table. This function is executed once for the entire statement, not for each row:

CREATE OR REPLACE FUNCTION after_insert_film_statement_trigger() RETURNS TRIGGER AS $$ 
BEGIN 
RAISE NOTICE 'A batch of films has been inserted into the film table'; RETURN NEW; -- Proceed with the statement 
END; $$ LANGUAGE plpgsql;

Step 2: Create the Trigger

Next, create a trigger (drop the trigger if it exists already) that will call the above function after any insert operation on the film table. This trigger is defined to fire after the entire insert statement:

DROP TRIGGER IF EXISTS after_insert_film_statement ON film;

CREATE TRIGGER after_insert_film_statement 
AFTER INSERT ON film 
FOR EACH STATEMENT 
EXECUTE FUNCTION after_insert_film_statement_trigger();

Step 3: Insert Values

You can now perform an insert operation that includes multiple rows at once. The trigger will activate, logging the action for the entire insert statement:

INSERT INTO film VALUES  
(61094, 'Movie Title 1', 'Description 1', 2021, 1, 3, 4.99, 120, 7.99, 'PG-13', CURRENT_TIMESTAMP, '{Trailers}', 'Sample Text 1'), 
(61095, 'Movie Title 2', 'Description 2', 2022, 1, 5, 5.99, 130, 5.89, 'PG', CURRENT_TIMESTAMP, '{Trailers}', 'Sample Text 2');

Step 4: See the RAISE NOTICE Message

After executing the insert statement, you should see the raised notice message in your console, indicating that a batch of films has been inserted:

NOTICE: A batch of films has been inserted into the film table

Additional DML Trigger for INSERT with Validation

Before Trigger: Validates the release year of the film.

CREATE FUNCTION

CREATE OR REPLACE FUNCTION validate_film_year() 
RETURNS TRIGGER AS $$ 
BEGIN
 
IF NEW.release_year > EXTRACT(YEAR FROM CURRENT_DATE) 
THEN RAISE EXCEPTION 'Release year cannot be in the future'; 
END IF; 

RETURN NEW; -- Proceed with the insert 

END; $$ LANGUAGE plpgsql; 

CREATE TRIGGER

CREATE TRIGGER film_year_check 
BEFORE INSERT ON film 
FOR EACH ROW 
EXECUTE FUNCTION validate_film_year();  

INSERT VALUES -- THIS WILL RAISE AN EXCEPTION

INSERT INTO film VALUES (61099, 'Movie Title 1', 'Description 1', 2026, 1,3, 4.99, 120, 7.99, 'PG-13', CURRENT_TIMESTAMP, '{Trailers}', 'Sample Text 1');

2. DML Trigger on UPDATE

Row-Level Trigger

Before Trigger : Validates data or generate logs before updating.

Step 1: Create a Function

First, you define a trigger function that updates the last_update timestamp whenever a row in the actor table is updated:

CREATE OR REPLACE FUNCTION update_actor_last_updated() RETURNS TRIGGER AS $$ 
BEGIN 
NEW.last_update := CURRENT_TIMESTAMP; 
RETURN NEW; -- Allow update to proceed 
END; $$ LANGUAGE plpgsql;

Step 2: Drop Any Old Trigger if Existing

Before creating a new trigger, it’s a good practice to drop any existing trigger of the same name to avoid conflicts:

DROP TRIGGER IF EXISTS update_last_updated_row ON actor;

Step 3: Create the Trigger

Now, create a new trigger that will call the above function before any update operation on the actor table:

CREATE TRIGGER update_last_updated_row 
BEFORE UPDATE ON actor 
FOR EACH ROW  EXECUTE FUNCTION update_actor_last_updated();

Step 4: Update Values

You can now perform an update operation on the actor table. This will trigger the function to update the last_update column:

UPDATE actor SET first_name = 'Alice' WHERE first_name = 'Sheba';

Step 5: Verify the Update

After executing the update statement, you can select the updated rows to verify the change and check the last_update timestamp:

SELECT * FROM actor WHERE first_name = 'Alice';

Row-Level Trigger

After Trigger : Validates data or logs completion after updating.

Step 1: Create the Audit Log Table

First, create a table to store the audit logs for updates made to the actor table:

CREATE TABLE audit_log (
log_id SERIAL PRIMARY KEY, 
updated_at TIMESTAMP, 
total_updated_rows INTEGER );

Step 2: Create a Function

Next, define a trigger function that logs the update details into the audit_log table whenever a row in the actor table is updated:

CREATE OR REPLACE FUNCTION log_actor_updates() 
RETURNS TRIGGER AS $$ 

DECLARE      
row_count INTEGER; 

BEGIN      

SELECT COUNT(*) INTO row_count 
FROM actor 
WHERE first_name = NEW.first_name; 

INSERT INTO audit_log (updated_at, total_updated_rows) VALUES (CURRENT_TIMESTAMP, row_count); 

RETURN NEW; -- Proceed with the update operation 

END; $$ LANGUAGE plpgsql;

Step 3: Drop Any Old Trigger if Existing

Before creating a new trigger, it’s a good practice to drop any existing trigger of the same name to avoid conflicts:

DROP TRIGGER IF EXISTS log_actor_update_statement ON actor;

Step 4: Create the Trigger

Now, create a new trigger that will call the above function after any update operation on the actor table:

CREATE TRIGGER log_actor_update_statement 
AFTER UPDATE ON actor 
FOR EACH ROW  
EXECUTE FUNCTION log_actor_updates();

Step 5: Update Values

You can now perform an update operation on the actor table. This will trigger the function to log the update details:

UPDATE actor SET first_name = 'Sheba' WHERE first_name = 'Alice';

Step 6: Verify the Audit Log

After executing the update statement, you can check the audit_log table to see the entries that were logged:

SELECT * FROM audit_log;

Step 7: Verify the Updated Values

Finally, you can check the updated rows in the actor table to confirm the changes:

SELECT * FROM actor WHERE first_name = 'Sheba';

Statement-Level Trigger

Before Trigger: Validates or Logs an action to the update statement before updating.

Step 1: Create a Function

First, you define a trigger function that logs a message before any update operation on the department table:

CREATE OR REPLACE FUNCTION log_before_update_department() RETURNS TRIGGER AS $$ 
BEGIN 
RAISE NOTICE 'About to update rows in the department table'; 
RETURN NULL; -- Statement-level trigger, no row-level processing 
END; $$ LANGUAGE plpgsql;

Step 2: Drop Any Old Trigger if Existing

It's always a good practice to drop any existing trigger of the same name to avoid conflicts:

DROP TRIGGER IF EXISTS before_update_department_statement ON department;

Step 3: Create the Trigger

Now, create a new before update trigger that will fire before any update operation on the department table:

CREATE TRIGGER before_update_department_statement 
BEFORE UPDATE ON department 
FOR EACH STATEMENT 
EXECUTE FUNCTION log_before_update_department();

Step 4: Update Values

Perform an update operation on the department table:

UPDATE department SET budget = budget * 1.05;

Step 5: Verify the Trigger Logs

Upon performing the update, you'll see the following output from the trigger:

NOTICE: About to update rows in the department table

Statement-Level Trigger

After Trigger: Validates or Logs an action to the update statement after updating.

Step 1: Create a Function

Now, define a trigger function that logs a message after any update operation on the department table:

CREATE OR REPLACE FUNCTION log_after_update_department() RETURNS TRIGGER AS $$ 
BEGIN 
RAISE NOTICE 'Update complete on the department table'; 
RETURN NULL; -- Statement-level trigger, no row-level processing 
END; $$ LANGUAGE plpgsql;

Step 2: Drop Any Old Trigger if Existing

Drop any old trigger of the same name:

DROP TRIGGER IF EXISTS after_update_department_statement ON department;

Step 3: Create the Trigger

Now, create a new after update trigger that will fire after any update operation on the department table:

CREATE TRIGGER after_update_department_statement 
AFTER UPDATE ON department 
FOR EACH STATEMENT 
EXECUTE FUNCTION log_after_update_department();

Step 4: Update Values

Again, perform an update operation:

UPDATE department SET budget = budget * 1.05;

Step 5: Verify the Trigger Logs

After performing the update, you should see the following output:

NOTICE: Update complete on the department table

3. DML Trigger on DELETE

Row-Level Trigger

Before Trigger : Validates data or generate logs before deletion.

Step 1: Create a Function

First, you define a trigger function that prevents the deletion of rows where the first_name is 'Sheba':

CREATE OR REPLACE FUNCTION prevent_delete_actor() 
RETURNS TRIGGER AS $$ 
BEGIN 
IF OLD.first_name = 'Sheba' THEN RAISE EXCEPTION 'Deletion of Sheba is not allowed!'; 
END IF; 
RETURN OLD; -- Allow the deletion to proceed for other rows 
END; $$ LANGUAGE plpgsql;

Step 2: Drop Any Old Trigger if Existing

Drop any old trigger to avoid conflicts:

DROP TRIGGER IF EXISTS before_actor_delete_trigger ON actor;

Step 3: Create the Trigger

Now, create a before delete trigger that will fire before any row is deleted from the actor table:

CREATE TRIGGER before_actor_delete_trigger 
BEFORE DELETE ON actor 
FOR EACH ROW  
EXECUTE FUNCTION prevent_delete_actor();

Step 4: Delete Values

Now, attempt to delete a row where the first_name is 'Sheba' (this will trigger the exception):

DELETE FROM actor WHERE first_name = 'Sheba';

Step 5: Verify the Trigger Logs

When attempting to delete, you will see the following error message:

ERROR:  Deletion of Sheba is not allowed!

Rows with other first names will still be deletable.

Row-Level Trigger

After Trigger : Validates data or logs completion after deletion.

Step 1: Create an Audit Table

First, you need an audit table to store the details of deleted rows:

CREATE TABLE actor_audit ( audit_id SERIAL PRIMARY KEY, actor_id INTEGER, first_name VARCHAR(50), last_name VARCHAR(50), deleted_at TIMESTAMP );

Step 2: Create a Function

Now, define a trigger function that logs the details of each deleted row into the audit table:

CREATE OR REPLACE FUNCTION log_deleted_actor() 
RETURNS TRIGGER AS $$ 
BEGIN -- Insert the deleted row details into the audit table INSERT INTO actor_audit (actor_id, first_name, last_name, deleted_at) VALUES (OLD.actor_id, OLD.first_name, OLD.last_name, CURRENT_TIMESTAMP); RETURN OLD; -- Return the deleted row 
END; $$ LANGUAGE plpgsql;

Step 3: Drop Any Old Trigger if Existing

Drop any old trigger to avoid conflicts:

DROP TRIGGER IF EXISTS after_actor_delete_trigger ON actor;

Step 4: Create the Trigger

Now, create an after delete trigger that fires after each row is deleted from the actor table:

CREATE TRIGGER after_actor_delete_trigger 
AFTER DELETE ON actor 
FOR EACH ROW  
EXECUTE FUNCTION log_deleted_actor();

Step 5: Delete Values

Now, attempt to delete rows from the actor table. This will trigger the function to log the deleted rows:

DELETE FROM actor WHERE first_name = 'Alice';

Step 6: Verify the Audit Log

After executing the delete statement, check the actor_audit table to verify the logs of the deleted rows:

SELECT * FROM actor_audit;

You should see an entry for each deleted row, with details like actor_id, first_name, last_name, and the deleted_at timestamp.

Statement-Level Trigger

Before Trigger: Validates or Logs an action to the delete statement before deletion.

Step 1: Create the Function

This function will log a message indicating that a delete operation is going to be performed.

CREATE OR REPLACE FUNCTION log_before_delete() 
RETURNS TRIGGER AS $$ 
BEGIN -- Log a message indicating that a delete is going to be performed RAISE NOTICE 'Delete operation is going to be performed on the actor table.'; 
RETURN NULL; -- Allow the deletion to proceed 
END; $$ LANGUAGE plpgsql;

Step 2: Create the Trigger

Now, create the trigger that will call the above function before any delete operation on the actor table.

DROP TRIGGER IF EXISTS before_actor_delete ON actor;

CREATE TRIGGER before_actor_delete 
BEFORE DELETE ON actor 
FOR EACH STATEMENT 
EXECUTE FUNCTION log_before_delete();

Step 3: Testing the Trigger

Now, let's delete some rows from the actor table to see how the trigger behaves.

DELETE FROM actor WHERE first_name = 'Alice' OR first_name = 'Bob';

After executing the delete statement, you should see the following message printed in the console:

NOTICE:  Delete operation is going to be performed on the actor table.

Statement-Level Trigger

After Trigger: Validates or Logs an action to the delete statement after deletion.

Step 1: Create the Function

The function will log the number of rows remaining in the actor table after rows are deleted.

CREATE OR REPLACE FUNCTION log_after_delete_actor() 
RETURNS TRIGGER AS $$ 
DECLARE 
remaining_actors INTEGER; 
BEGIN -- Count the remaining rows in the actor table 
SELECT COUNT(*) INTO remaining_actors FROM actor; -- Log a message with the number of remaining actors 
RAISE NOTICE 'Rows deleted. Remaining actors: %', remaining_actors; RETURN NULL; -- No need to return anything since the deletion is already done 
END; $$ LANGUAGE plpgsql;

Step 2: Drop the Old Trigger (if it exists)

Before creating a new trigger, drop any existing one to avoid conflicts.

DROP TRIGGER IF EXISTS after_delete_actor_trigger ON actor;

Step 3: Create the Trigger

Now, create the after delete trigger that will call the log_after_delete_actor() function after any delete operation occurs on the actor table.

CREATE TRIGGER after_delete_actor_trigger 
AFTER DELETE ON actor 
FOR EACH STATEMENT 
EXECUTE FUNCTION log_after_delete_actor();

Step 4: Delete Rows to Test the After Delete Trigger

Let's delete rows from the actor table to trigger the after_delete_actor_trigger.

DELETE FROM actor WHERE first_name = 'Alice';

Step 5: Expected Output of the After Delete Trigger

After executing the delete statement, the trigger will log how many rows remain in the actor table. You should see the following notice in your console:

NOTICE: Rows deleted. Remaining actors: [remaining row count]

DML Trigger Combinations Table

DML	Trigger Level	Trigger Type	Logging	Validation (Before)	Validation (After)	Pre-Analysis & Backup (Before)	Result Analysis Tracking (After)
Insert	Row	Before	✔️	✔️		✔️
Insert	Row	After	✔️		✔️		✔️
Insert	Statement	Before	✔️	✔️		✔️
Insert	Statement	After	✔️		✔️		✔️
Update	Row	Before	✔️	✔️		✔️
Update	Row	After	✔️		✔️		✔️
Update	Statement	Before	✔️	✔️		✔️
Update	Statement	After	✔️		✔️		✔️
Delete	Row	Before	✔️	✔️		✔️
Delete	Row	After	✔️		✔️		✔️
Delete	Statement	Before	✔️	✔️		✔️
Delete	Statement	After	✔️		✔️		✔️

Column Definitions

Logging: If you need to record actions taken by the trigger for auditing or monitoring, implement logging in these trigger (marked ✔️) . Use logging triggers to maintain an audit trail of DML operations.
Validation (Before): Use these trigger (marked ✔️) if you need to perform validation checks before executing the DML operation. This helps ensure data integrity and business rules are followed before any changes are made to the database.
Validation (After): Implement these trigger (marked ✔️) when you require validation after the DML operation to confirm that the operation was successful or to verify that the data meets certain conditions post-execution.
Pre-Analysis & Backup (Before): If you need to analyze data or create backups before executing the DML operation, use these trigger (marked ✔️) . This allows you to assess the state of the data or safeguard it against potential loss during the operation.
Result Analysis Tracking: Utilize these trigger (marked ✔️) if you want to track and analyze the outcome of the operation after execution. This is essential for capturing the effects of the DML command, which can be useful for reporting or further analysis.

Best Practices

Use triggers sparingly: Overusing triggers can make debugging difficult and degrade performance.
Document your triggers: Always document what each trigger does and why it exists to help future developers understand its purpose.
Test your triggers: Ensure you thoroughly test triggers in a development environment before deploying to production to avoid unintended consequences.

SQL triggers are powerful tools that can greatly enhance the functionality of your database. Understanding the types of triggers, their execution levels, and timings allows you to enforce data integrity, automate workflows, and log changes effectively. Whether you're a beginner or an advanced user, mastering triggers will empower you to write cleaner, more efficient SQL code and help maintain a robust database environment.

By following these guidelines and understanding the hierarchical structure of SQL triggers, you can leverage their capabilities to ensure your database operates smoothly and efficiently.

A Comprehensive Guide to SQL Triggers: DML Triggers - PART I

Why Use SQL Triggers?

Understanding SQL Triggers: Structure and Hierarchy

SQL Trigger Structure

Hierarchy of SQL Triggers

1. DML Trigger for INSERT :

Row-Level Trigger

Before Trigger : Validates data or generate logs before insertion.

Step 1: Create a Function

Row-Level Trigger

After Trigger : Validates data or logs completion after insertion.

Statement-Level Trigger

Before Trigger: Validates or Logs an action to the entire insert statement before insertion.

Statement-Level Trigger

After Trigger: Validates or Logs an action to the entire insert statement after insertion.

Additional DML Trigger for INSERT with Validation

2. DML Trigger on UPDATE

Row-Level Trigger

Before Trigger : Validates data or generate logs before updating.

Row-Level Trigger

After Trigger : Validates data or logs completion after updating.

Statement-Level Trigger

Before Trigger: Validates or Logs an action to the update statement before updating.

Step 1: Create a Function

Step 2: Drop Any Old Trigger if Existing

Step 3: Create the Trigger

Step 4: Update Values

Step 5: Verify the Trigger Logs

Statement-Level Trigger

After Trigger: Validates or Logs an action to the update statement after updating.

Step 1: Create a Function

Step 2: Drop Any Old Trigger if Existing

Step 3: Create the Trigger

Step 4: Update Values

Step 5: Verify the Trigger Logs

3. DML Trigger on DELETE

Row-Level Trigger

Before Trigger : Validates data or generate logs before deletion.

Step 1: Create a Function

Step 2: Drop Any Old Trigger if Existing

Step 3: Create the Trigger

Step 4: Delete Values

Step 5: Verify the Trigger Logs

Row-Level Trigger

After Trigger : Validates data or logs completion after deletion.

Step 1: Create an Audit Table

Step 2: Create a Function

Step 3: Drop Any Old Trigger if Existing

Step 4: Create the Trigger

Step 5: Delete Values

Step 6: Verify the Audit Log

Statement-Level Trigger

Before Trigger: Validates or Logs an action to the delete statement before deletion.

Statement-Level Trigger

After Trigger: Validates or Logs an action to the delete statement after deletion.

DML Trigger Combinations Table

Column Definitions

Best Practices

Recent Posts