Delete SQL Trigger – Syntax and Examples

Syntax and Parameters of SQL DELETE Trigger

The basic syntax used for writing a DELETE Trigger in SQL is:

CREATE TRIGGER [schema_name.] trigger_name ON table_name
{ AFTER|BEFORE|INSTEAD OF } DELETE AS
{ SQL statements }

The parameters used in the above syntax are explained below:

• schema_name: The name of the schema where the trigger belongs to. This parameter is optional.
• trigger_name: The unique name of the trigger that is used to reference the trigger. This parameter is required.
• table_name: The name of the table on which the trigger is created. This parameter is required.
• AFTER | BEFORE | INSTEAD OF: This specifies when the trigger is fired, either after, before, or instead of the DELETE statement.
• SQL statements: These are the statements executed when the trigger is fired.

Rewording: SQL Trigger Instances for DELETE Operation.

To illustrate how DELETE Triggers work in SQL, let us create two dummy tables called students and students_audit, respectively. The students_audit table is used to store the deleted records from the students table. The following SQL statements can be used to create the tables:

CREATE TABLE students (id INT, name VARCHAR(50), grade INT);

CREATE TABLE students_audit (id INT, name VARCHAR(50), grade INT, deleted_date DATE);

Now, let’s create a DELETE trigger that captures the deleted records into the students_audit table:

CREATE TRIGGER delete_student
ON students
AFTER DELETE
AS
INSERT INTO students_audit(id, name, grade, deleted_date)
SELECT id, name, grade, GETDATE()
FROM deleted;

The above trigger inserts the deleted records (id, name, grade, and current timestamp) into the students_audit table when a record is deleted from the students table.

Delete Triggers in SQL are essential in automating database management processes. The syntax for creating triggers is simple to understand, and once written, it saves time during routine database activities. Using examples like the ones above, developers can handle an endless number of delete scenarios efficiently.

Suggested readings.

• An Introduction to Triggers in SQL Server
• Triggers in SQL
• CREATE TRIGGER (Transact-SQL)

Syntax of SQL DELETE Trigger

The syntax of SQL DELETE Trigger is as follows:

CREATE TRIGGER [ schema_name. ] trigger_name ON table_name {AFTER | BEFORE | INSTEAD OF} DELETE AS {SQL statements}

The schema_name is an optional parameter that determines the schema of the table where the trigger is located, and the trigger_name is the name of the trigger that you want to create. The table_name is the table where the trigger is located, and the SQL statements are the statements that are executed when the trigger is fired.

SQL DELETE Trigger Examples.

Let us create two dummy tables called students and students_audit, respectively, to illustrate how DELETE Triggers work in SQL.

• students Table:

StudentID	StudentName	EmailID	Age
1	John	[email protected]	20
2	Jane	[email protected]	22

• students_audit Table:

StudentID	StudentName	Action
1	John	DELETE

In this example, the trigger will be fired after a DELETE operation is performed on the students table. If a row is deleted from the students table, the trigger will insert a record into the students_audit table, indicating that a record was deleted.

DELETE Triggers are a powerful feature in SQL that allow you to execute statements automatically when a DELETE operation is performed on a table. By using DELETE Triggers, you can easily create an audit trail or enforce referential integrity in your database. With the knowledge presented in this article, you can now create your own DELETE Triggers in SQL.

Recommended Articles

• How to Use the SQL Server DELETE Trigger for Audit Trail
• SQL Server DELETE Trigger
• SQL Trigger Tutorial

Arguments of SQL DELETE Trigger

The arguments of SQL DELETE Trigger are:

• IF EXISTS – an optional parameter used to check if the trigger exists before deleting it. This prevents errors from occurring when deleting a trigger that does not exist.
• TRIGGER_NAME – the name of the trigger that we want to delete. It is important to specify the correct trigger name to avoid deleting the wrong trigger.
• TABLE_NAME – the name of the table where the trigger is located. This is necessary because different tables may have different triggers that need to be managed.

Remarks of SQL DELETE Trigger

When working with SQL DELETE Triggers, there are a few important remarks to keep in mind:

• Only a superuser can delete triggers created by other users.
• You cannot use an SQL DELETE Trigger statement to delete system triggers.
• Once you have deleted a trigger, there is no way to undo the action.

Permissions in SQL DELETE Trigger

When using SQL DELETE triggers, certain permissions are required:

• In order to remove a trigger, you must be the owner of the trigger.
• For removing other users’ triggers, superuser privileges are required.

It is important to have the correct permissions in order to properly manage SQL DELETE triggers and maintain the integrity and security of your database.

Examples of SQL DELETE Trigger

Example 1: Deleting a DML Trigger

If we have created a DML trigger called trg_customers_dml on the Customers table, we can remove it using the following SQL statement:

DROP TRIGGER trg_customers_dml ON Customers;

Example 2: Deleting a DDL Trigger

Suppose we have another trigger called trg_customers_ddl on the Customers table, which is a DDL trigger. To remove it, we can execute the following SQL statement:

DROP TRIGGER trg_customers_ddl ON Customers;

Note: To drop any type of trigger, we must have proper permissions. For instance, to drop a DML trigger, we must have the ‘ALTER’ permission on the table it is created on. Similarly, to drop a DDL trigger defined with server scope or a logon trigger, we need ‘CONTROL SERVER’ permission in the server, and to drop a DDL trigger with database scope, we need the ‘ALTER ANY DATABASE DDL TRIGGER’ permission.

Conclusion

Deleting SQL triggers can be done easily with the use of the DROP TRIGGER statement, and with the proper permissions on the table or view where the trigger is defined. It is important to understand the basic syntax and parameters used in creating SQL delete triggers, as well as the differences between DDL triggers and DML triggers. Always remember to expand the database, tables, and triggers before deleting the trigger object. With this knowledge, you can confidently manage your SQL triggers and keep your database running smoothly.

References

• PostgreSQL Documentation – Triggers
• SQL Tutorial – MySQL Triggers

A Delete SQL Trigger is a data manipulation language (DML) trigger that is invoked when a user attempts to delete data from a specific table. This trigger fires automatically before or after a user issues a DELETE command for a specified database object. When the trigger is fired, a stored procedure is called, and the SQL script is executed. The Delete SQL Trigger allows database administrators to implement business rules by performing custom actions such as auditing changes, updating specific columns, or preventing specific data from being removed from the database.

The basic syntax for creating a Delete SQL Trigger in SQL is as follows:

CREATE TRIGGER [schema_name.] trigger_name 
ON table_name  
{AFTER|BEFORE|INSTEAD OF} DELETE 
AS 
{SQL statements}

Here is an example of a SQL DELETE Trigger:

CREATE TRIGGER TRG_DEL_Student
ON Students
AFTER DELETE
AS
INSERT INTO Students_Audit(StudentID, FullName, Course, DeletedDate)
SELECT 
    StudentID,
    FullName,
    Course,
    GETDATE() DeletedOn
FROM
    deleted

In the example above, a trigger named TRG_DEL_Student is created to audit any data deleted from the Students table. The trigger fires after each delete operation, and it captures the ID, full name, course, and the deletion timestamp for the deleted record.

To drop a Delete SQL Trigger, you can use the DROP TRIGGER statement. Here is the syntax:

DROP TRIGGER [IF EXISTS] trigger_name [, ...n] ON {DATABASE|ALL SERVER};

For example, the following SQL statement drops the trigger named TRG_DEL_Student:

DROP TRIGGER TRG_DEL_Student;

It is important to note that dropping a DML trigger requires ALTER permission on the table or view on which the trigger is defined, whereas dropping a DDL trigger defined with server scope (ON ALL SERVER) or a logon trigger requires CONTROL SERVER permission in the server.