Triggers In SQL

Triggers In SQL

A trigger is essentially a special type of stored procedure that can be executed in response to one of three conditions:

• INSERT
• UPDATE
• DELETE

The Transact-SQL syntax to create a trigger looks like this:

CREATE TRIGGER TRIGGER_NAME
ON TABLE_NAME
FOR{INSERT, UPDATE, DELETE}
AS SQL_STATEMENTS

Triggers are most useful for enforcing referential integrity. The referential integrity enforces rules used to ensure that data remains valid across multiple tables.
Suppose a user entered the following command:

insert into Orders(OrderId, OrderName, Quantity, CustomerId) values(1, 'SQL Books', 2, 1)
GO

This perfectly valid SQL statement inserts a new record in the Orders table. However, a quick check of the Customers table shows that there is no CustomerId =1. A user with insert privileges in the Orders table can completely destroy your referential integrity.

Triggers and Transactions

The actions executed within a trigger are implicitly executed as part of a transaction. Here's the broad sequence of events:

1. The BEGIN TRANSACTION statement is implicitly issued (for tables with triggers).
2. The INSERT, UPDATE, or DELETE operation occurs.
3. The trigger is called and its statements are executed.
4. The trigger either rolls back the transaction or the transaction is implicitly committed.

Examples

This example illustrates the solution to the Orders table update problem mentioned earlier.

CREATE TRIGGER CHECK_CUSTOMERS
ON ORDERS
FOR INSERT, UPDATE AS
IF NOT EXISTS(SELECT * FROM CUSTOMERS, ORDERS
WHERE CUSTOMERS.CUSTOMER_ID=ORDERS.CUSTOMER_ID)
BEGIN
PRINT 'ILLEGAL CUSTOMER_ID'
ROLLBACK TRANSACTION
END
GO

A similar problem could exists for deletes from the ORDERS table. Suppose that when you delete an customer's only record from the ORDERS table, you also want to delete the customer from the CUSTOMERS table. If the records have already been deleted when the trigger is fired, how do you know which CUSTOMER_ID should be deleted? There are two methods to solve this problem:

First, Delete all the customers from the CUSTOMERS table who no longer have any orders in the ORDERS table.

CREATE TRIGGER DELETE_CUSTOMERS
ON ORDERS
FOR DELETE AS
BEGIN
DELETE FROM CUSTOMERS WHERE CUSTOMER_ID NOT IN(
SELECT CUSTOMER_ID FROM ORDERS
)
END
GO

Second, Examine the deleted logical table. Transact-SQL maintains two tables: DELETED and INSERTED. These tables, which maintain the most recent changes to actual table, have the same structure as the table on which the trigger is created. Therefore, you could retrieve the customer ID's from the DELETED table and then delete these ID's from the CUSTOMERS table.

CREATE TRIGGER DELETE_CUSTOMERS
ON ORDERS
FOR DELETE AS
BEGIN
DELETE CUSTOMERS FROM CUSTOMERS, DELETED
WHERE CUSTOMERS.CUSTOMER_ID = DELETED.CUSTOMER_ID 
END
GO

Restrictions On Using Triggers

The trigger is a powerful feature of the relational database that promotes flexibility and maximized control of data processing. However, triggers do have their limitations. You must observe the following restrictions when you use triggers:

• Triggers cannot be created on temporary tables.
• Triggers must be created on tables in the current database.
• Triggers cannot be created on views.
• When a table is dropped, all triggers associated with that table are automatically dropped with it.