Triggers in SQL Server 2005

Hello mates… I am starting to float some light on the Sql server triggers. Lets Start…
A trigger is a special kind of stored procedure they automatically executes when an event occurs in the database server are used to enforce data integrity and business rules,prevent incorrect,unauthorized and inconsistent change in data. Triggers allows to perform cascading delete, update operations. If constraints exist on the trigger table,they are checked first,then triggers executed. There are DML, DDL, or logon trigger.

Creation of Trigger :

DML Trigger :

CREATE TRIGGER [SchemaName.]TriggerName
ON { table | view }
{ FOR | AFTER | INSTEAD OF }
{ INSERT/UPDATE/DELETE}
AS {SQL Statment }

Example Create Trigger with name ‘utrgCustUpdateMail’

CREATE TRIGGER utrgCustUpdateMail
— and the table is CustomerDetails
ON CustomerInfo
— trigger is fired when an update is made for the table
FOR UPDATE
AS
declare @CustomerID varchar(25)
declare @body varchar(1500)
declare @CustomerName varchar(25)
declare @CustomerNewName varchar(25)

— gets the previous customer first name that was deleted
SELECT @CustomerID = Customer_id,@CustomerName = d.First_Name FROM deleted d

— gets the new customer first name
SELECT @CustomerNewName = First_Name FROM inserted

SET @body = ‘Customer with ID=’ @CustomerID ‘ has been updated
with previous First Name is ‘ @CustomerName ‘
and the new First Name is ‘ @CustomerNewName

–xp_sendmail is the extended sproc used to send the mail
EXEC master..xp_sendmail
@recipients = ‘ali_raza_shaikh@datasprings.com’,
@subject = ‘Customer Information Updated’,
@message = @body
GO

Logon Events Trigger :
Triggers for Logon Events (New in Service Pack 2)

With SP2, triggers can now fire on Logon events as well as DML or DDL events.

Logon triggers can help complement auditing and compliance. For example, logon events can be used for enforcing rules on connections (for example limiting connection through a specific username or limiting connections through a username to a specific time periods) or simply for tracking and recording general connection activity. Just like in any trigger, ROLLBACK cancels the operation that is in execution. In the case of logon event that means canceling the connection establishment. Logon events do not fire when the server is started in the minimal configuration mode or when a connection is established through dedicated admin connection (DAC).

The following code snippet provides an example of a logon trigger that records the information about the client connection.

CREATE TRIGGER connection_limit_trigger
ON ALL SERVER FOR LOGON
AS
BEGIN
INSERT INTO logon_info_tbl SELECT EVENTDATA()
END;
You can find more information about this feature in updated Books Online for SQL Server Services Pack 2 un the heading “Logon Triggers”.

Server and Database Level DDL Triggers :

This is the solution when System Admin himself making accidental mistakes use Server and Database Level DDL Triggers.

Data Definition Level contains schema of the database object. Definition of DDL Trigger (from BOL) is DDL Triggers are a such special kind of trigger that executes in response to Data Definition Language (DDL) statements. They can be used to perform tasks as auditing and regulating database operations.

different between DML and DDL triggers : DML(Data Manipulation Level) contains the actual data which is stored in the database schema. UPDATE, INSERT, DELETE, SELECT are clause are used to manipulate database. This is notifiable that

* Only DML triggers triggers support INSTEAD OF feature not DDL.
DDL triggers does not participate in database manipulations like Insert, Update.
Following example demonstrates how DDL trigger can be used to prevent dropping stored procedure.

Example : Prevent DROP PROCEDURE Command
Illustrated in Three Steps

Step 1 :
Create sample stored procedure.

CREATE PROCEDURE uspTest
AS
SELECT 100 TempField;
GO

Step 2 :

Create DDL trigger which will prevent dropping the stored procedure.

CREATE TRIGGER utrgSPDropPrevent ON DATABASE

FOR DROP_PROCEDURE
AS
PRINT ‘Dropping Procedure is not allowed.We are preventing drop opration by DDL Trigger.
To drop stored procedure run following Step.

DISABLE TRIGGER utrgSPDropPrevent ON DATABASE;
;
ENABLE TRIGGER utrgSPDropPrevent ON DATABASE;’

ROLLBACK;

Step 3 :

Now test above trigger by attempting to drop the stored procedure.

DROP PROCEDURE uspTest;

OUTPUT :
Dropping Procedure is not allowed.We are preventing drop opration by DDL Trigger.
To drop stored procedure run following Step.

DISABLE TRIGGER utrgSPDropPrevent ON DATABASE;
;
ENABLE TRIGGER utrgSPDropPrevent ON DATABASE;’

Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.

Step 4 :

Now DISABLE and drop the Proc and then Enable again.

DISABLE TRIGGER utrgSPDropPrevent ON DATABASE;
DROP PROCEDURE uspTest;
ENABLE TRIGGER utrgSPDropPrevent ON DATABASE;

Source of Triggere is MSDN.

Cheers …….! Happy Coding. Neeraj