DATABASE TRIGGER
This is can be defined as a name stored procedure. This is created on a table and is fired based on database events (INSERT, UPDATE, DELETE).
[A trigger is a database object that is Bind to a table. In many aspects it is similar to a stored procedure and are often referred to as a "special kind of stored procedure." SQL Server 2000 greatly enhances trigger functionality, extending the capabilities of the triggers you already know and love, and adding a whole new type of trigger, the "Instead Of" trigger.
When to Use Triggers
There are many reasons to use triggers. If you have a table which keeps a log of messages, you may want to have a copy of them mailed to you if they are urgent. If there were no triggers you would have some solutions, though they are not as elegant. You could modify the application(s) logging the messages. This means that you might be redundantly coding the same thing in every application that logs messages.
Tables can have multiple triggers. The CREATE TRIGGER statement can be defined with the FOR UPDATE, FOR INSERT, or FOR DELETE clauses to target a trigger to a specific class of data modification actions. When FOR UPDATE is specified, the IF UPDATE (column_name) clause can be used to target a trigger to updates affecting a particular column.
SQL Server 2000 greatly enhances trigger functionality, extending the capabilities of the triggers you already know and love, and adding a whole new type of trigger, the "Instead Of" trigger.]
DELETED AND INSERTED TABLES
Two special tables are used in trigger statements, the ‘deleted’ table and ‘inserted’ table. Microsoft SQL Saver 2000 automatically created and manages these tables. You can use these temporary memories, resident tables to test the effects of certain data modifications and to set conditions for trigger actions, however, you cannot alter the data in the table directly.
UPDATE TRIGGER
This fired when it encounters an update command with a particular condition.
Column level Update Trigger
CREATE TRIGGER T1 ON STUDENTS
FOR UPDATE
AS
IF (SELECT AGE FROM INSERTED)>10
BEGIN
PRINT ‘AGE SHOULD NOT BE LESS THAN 10’
ROLLBACK TRANSACTION
END
Checking the trigger method: UPDATE STUDENTS SET AGE=8 WHERE
ENAME='SANTHOSH'
Table Level Update Trigger
CREATE TRIGGER T2 ON STUDENTS
FOR UPDATE
AS IF UPDATE (AGE)
BEGIN
PRINT ‘AGE SHOULD NOT BE CHANGED’
ROLLBACK TRANSACTION
END
INSERT TRIGGER
This is fired when it encounters an insert command with a particular condition.
CREATE TRIGGER T3 ON EMPLOYEES
FOR INSERT
AS
IF (SELECT SAL FROM INSERTED)>2000
BEGIN
PRINT ‘SALARY SHOULD NOT BE LESS THAN 2000’
ROLLBACK TRANSACTION
END
DELETE TRIGGER
This is fired when it encounters an delete command with a particular condition
CREATE TRIGGER T4 ON EMPLOYEES
FOR DELETE
AS
IF (SELECT COUNT (*) FROM DELETED)>1
BEGIN
PRINT ‘MORE THAN ONE RECORD CAN NOT BE REMOVED AT A TIME’
ROLLBACK TRANSACTION
END
DROPPING A TRIGGER
DROP TRIGGER
Eg: DROP TRIGGER T1.
MUTATING A TRIGGER OR CASCADING TRIGGER
Created for one table affects another table
Eg: If we created a trigger in child table it affects Parent table also.
.png)
.png)
