A SQL Trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server.

A trigger is a special type of stored procedure because it is not called directly like a stored procedure. The main difference between a trigger and a stored procedure is that a trigger is called automatically when a data modification event is made against a table, whereas a stored procedure must be called explicitly.

Advantages of using SQL triggers

· SQL triggers provide an alternative way to check the integrity of data.

· SQL triggers can catch errors in business logic in the database layer.

· SQL triggers provide an alternative way to run scheduled tasks, by using SQL triggers, you don’t have to wait to run the scheduled tasks because the triggers are invoked automatically before or after a change is made to the data in the tables.

· SQL triggers are very useful to audit the changes of data in tables.

Disadvantages of using SQL triggers

· SQL triggers can only provide an extended validation and they cannot replace all the validations. Some simple validations have to be done in the application layer. For example, you can validate user’s inputs in the client side by using JavaScript or in the server side using server-side scripting languages such as JSP, PHP, ASP.NET, Perl, etc.

· SQL triggers are invoked and executed invisible from the client applications; therefore, it is difficult to figure out or DEBUG what happen in the database layer.

· SQL triggers may increase the overhead to the database server.

CREATE SQL Trigger

Trigger consists of:

· Header comments, Which includes author, date, purpose and change history with example.

· Trigger Name.

· Trigger Action. (FOR/BEFORE/AFTER INSERT, UPDATE, DELETE)

· Body of Trigger:

1. BEGIN

2. DECLARE Local variables

3. T-SQL [Collection of SQL Statements]

4. END

EXAMPLE: AFTER INSERT UPDATE DELETE

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Request]') AND type in (N'U'))

DROP TABLE [dbo].[Request]

CREATE TABLE [dbo].[Request](

[RequestId] [int] IDENTITY(1,1) NOT NULL,

[RequestDate] DateTime NULL DEFAULT GETDATE(),

[RequestType] [varchar](20) NULL,

[RequestStatusDate] DateTime NULL,

[Status] [varchar](20) NULL,

[Operation] [char](1) NULL,

CONSTRAINT [PK_Request_Id] PRIMARY KEY CLUSTERED

(

[RequestId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[TR_Request_AfterStatusInsert]'))

DROP TRIGGER [dbo].[TR_Request_AfterStatusInsert]

===========================================================================

AUTHOR : Govind

CREATE DATE : 17-NOV-2016

DESCRIPTION : Create trigger on table Request after Inserting

SNO ALTERED DATE AUTHOR DESCRIPTION

--- ------------ ------ -----------

SET STATISTICS TIME ON

SET STATISTICS TIME OFF

===========================================================================

CREATE TRIGGER [dbo].[TR_Request_AfterStatusInsert] ON [dbo].[Request]

AFTER INSERT, UPDATE, DELETE

BEGIN

SET NOCOUNT ON;

------------------ Trigger Operation ------------------------

DECLARE @Operation CHAR(1) = 'I'

IF EXISTS (SELECT 1 FROM inserted)

IF EXISTS (SELECT 1 FROM deleted)

SELECT @Operation = 'U'

ELSE

SELECT @Operation = 'I'

ELSE

IF EXISTS (SELECT 1 FROM deleted)

SELECT @Operation = 'D'

------------------ Request table varibles -------------------

DECLARE @RequestId INT = NULL

DECLARE @Status [VARCHAR] (20) = NULL

DECLARE @InsertedCount INT = 0,

@MaxRequestId INT = 0,

@CurrentRequestId INT = 0

SET @InsertedCount = (SELECT count(1) FROM inserted)

SET @MaxRequestId = (SELECT max(RequestId) FROM inserted);

WHILE (@CurrentRequestId <= @MaxRequestId AND @InsertedCount >= 1)

BEGIN

SET @CurrentRequestId = @CurrentRequestId + 1

SET @RequestId = 0

SET @Status = NULL

------------------ Setting the variable values ----------

SELECT TOP 1

@RequestId = RequestId,

@Status = [Status]

FROM inserted WITH (NOLOCK)

WHERE RequestId >= @CurrentRequestId

ORDER by RequestId

IF @RequestId IS NULL OR @RequestId = 0

CONTINUE

SET @CurrentRequestId = @RequestId

-- Updte RequestStatusDate when [Status] column is not null --

IF (@Status IS NOT NULL)

BEGIN

UPDATE dbo.Request SET

RequestStatusDate = GETDATE(),

Operation = @Operation

WHERE RequestId = @CurrentRequestId

END

END -- WHILE LOOP

END

INSERT [dbo].[Request] ([RequestType], [Status])

VALUES ('Import', 'Success'), ('Import', null)

INSERT [dbo].[Request] ([RequestType])

VALUES ('Import')

SELECT * FROM Request

UPDATE [dbo].[Request] SET [Status] = 'Failed' WHERE [Status] IS NULL

SELECT * FROM Request

ALTER SQL Trigger

Example for modifying the existing Trigger using the ALTER TRIGGER command is given below:

EXAMPLE:

ALTER TRIGGER [dbo].[TR_Request_AfterStatusInsert] ON [dbo].[Request]

EXECUTE SQL Trigger

EXAMPLE:

INSERT [dbo].[Request] ([RequestType], [Status])

VALUES ('Import', 'Success'), ('Import', null)

INSERT [dbo].[Request] ([RequestType])

VALUES ('Import')

DROP SQL Trigger

Example for deleting the existing SQL trigger using the DROP TRIGGER command is given below:

EXAMPLE 1:

DROP TRIGGER [dbo].[TR_Request_AfterStatusInsert]

EXAMPLE 2:

IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[TR_Request_AfterStatusInsert]'))

DROP TRIGGER [dbo].[TR_Request_AfterStatusInsert]

RENAME SQL Trigger

Example for renaming the existing SQL trigger (from TR_Request_AfterStatusInsert to TR_Request_AfterStatus) is given below:

IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[TR_Request_AfterStatusInsert]'))

AND NOT EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[TR_Request_AfterStatus]'))

BEGIN

EXEC sp_rename '[TR_Request_AfterStatusInsert]', '[TR_Request_AfterStatus]'

END

Another way of renaming the existing Trigger (from TR_Request_AfterStatusInsert to TR_Request_AfterStatus) is, CREATE a new Trigger, and then DROP the existing Trigger.

DISABLE SQL Trigger

Triggers are enabled by default when they are created. Disabling a trigger does not drop it. The trigger still exists as an object in the current database. However, the trigger does not fire when any Transact-SQL statements on which it was programmed are executed. Triggers can be re-enabled by using ENABLE TRIGGER. DML triggers defined on tables can be also be disabled or enabled by using ALTER TABLE.

EXAMPLE1:

DISABLE TRIGGER TR_Request_AfterStatusInsert ON Request

EXAMPLE2: (Using ALTER Table)

ALTER TABLE [dbo].[Request] DISABLE TRIGGER TR_Request_AfterStatusInsert

ENABLE SQL Trigger

A disabled trigger still exists as an object in the current database, but does not fire. Enabling a trigger causes it to fire when any Transact-SQL statements on which it was originally programmed are executed. Triggers are disabled by using DISABLE TRIGGER. DML triggers defined on tables can be also be disabled or enabled by using ALTER TABLE.

EXAMPLE1:

ENABLE TRIGGER TR_Request_AfterStatusInsert ON Request

EXAMPLE2: (Using ALTER Table)

ALTER TABLE [dbo].[Request] ENABLE TRIGGER TR_Request_AfterStatusInsert

Query the list of SQL Trigger

EXAMPLE 1:

SELECT * FROM sysobjects

WHERE xtype = 'TR' --'TR', FN, 'P', 'V'

EXAMPLE 2:

SELECT DISTINCT so.name, so.xtype, sc.TEXT

FROM syscomments sc

INNER JOIN sysobjects so ON sc.id=so.id

WHERE sc.TEXT LIKE '%Request%'

AND so.xtype = 'TR' --'TR', FN, 'P', 'V’

ENABLE / DISABLE SQL Trigger

EXAMPLE3:

DECLARE @Mode VARCHAR(20) = 'DISABLE' --'ENABLE'

DECLARE @CurrentId INT = 0, @MaxId INT = 0

DECLARE @Id INT = 0, @ParentId INT =0

DECLARE @Name VARCHAR(128) = '', @Sql NVARCHAR(500)

DECLARE @ParentObject VARCHAR(128) = '' -- for particular object, specify object name.

IF @ParentObject = ''

SET @ParentObject = NULL

SET @MaxId = (SELECT MAX(Id) FROM (SELECT id FROM sysobjects WHERE xtype = 'TR') sysobj)

SET @MaxId = ISNULL(@MaxId,0)

-- Loop

WHILE (@CurrentId <= @MaxId)

BEGIN

SET @Id = 0

SET @Name = NULL

SET @ParentId = 0

SELECT TOP 1

@Id = id,

@Name = name,

@ParentId = parent_obj

FROM (SELECT Id, name, parent_obj FROM sysobjects WHERE xtype = 'TR') sysobj

WHERE id > @CurrentId

ORDER BY id

IF (@Id IS NULL OR @Id = 0)

BREAK

SET @CurrentId = @Id

IF @ParentObject IS NOT NULL

BEGIN

IF @ParentId <> object_Id(@ParentObject) CONTINUE

END

--PRINT '------------------------------------'

BEGIN

PRINT @Mode + ' TRIGGER ' + @Name + ' ON '+ object_name(@ParentId)

SET @Sql = @Mode + ' TRIGGER ' + @Name + ' ON '+ object_name(@ParentId)

EXEC (@Sql)

END

List of Enabled Triggers

SELECT 'ENABLE TRIGGER ' + t.name + ' ON ' + p.Name + ';' FROM sysobjects t

INNER JOIN sysobjects p ON p.id = t.parent_obj

INNER JOIN sys.triggers et ON et.object_id = t.id

WHERE t.xtype = 'TR' AND et.is_disabled = 0

ORDER BY t.name

List of Disabled Triggers

SELECT 'DISABLE TRIGGER ' + t.name + ' ON '+ p.Name +';' FROM sysobjects t

INNER JOIN sysobjects p ON p.id = t.parent_obj

INNER JOIN sys.triggers et ON et.object_id = t.id

WHERE t.xtype = 'TR' AND et.is_disabled = 1

ORDER BY t.name

Enable the Disabled Triggers

SELECT 'ENABLE TRIGGER ' + t.name + ' ON '+ p.Name +';' FROM sysobjects t

INNER JOIN sysobjects p ON p.id = t.parent_obj

INNER JOIN sys.triggers et ON et.object_id = t.id

WHERE t.xtype = 'TR' AND et.is_disabled = 1

ORDER BY t.name

Disable the Enabled Triggers

SELECT 'DISABLE TRIGGER ' + t.name + ' ON '+ p.Name +';' FROM sysobjects t

INNER JOIN sysobjects p ON p.id = t.parent_obj

INNER JOIN sys.triggers et ON et.object_id = t.id

WHERE t.xtype = 'TR' AND et.is_disabled = 0

ORDER BY t.name

Execution Order of SQL Trigger

sp_settriggerorder specifies the AFTER triggers that are fired first or last. The AFTER triggers that are fired between the first and last triggers are executed in undefined order.

SYNTAX:

sp_settriggerorder [ @triggername = ] '[ triggerschema. ] triggername'

    , [ @order = ] 'value'

    , [ @stmttype = ] 'statement_type'

    [ , [ @namespace = ] { 'DATABASE' | 'SERVER' | NULL } ]

Arguments:

[ @triggername= ] '[ triggerschema.] triggername'

Is the name of the trigger and the schema to which it belongs, if applicable, whose order is to be set or changed. [triggerschema.]triggername is sysname. If the name does not correspond to a trigger or if the name corresponds to an INSTEAD OF trigger, the procedure returns an error. triggerschema cannot be specified for DDL or logon triggers.

[ @order= ] 'value'

Is the setting for the new order of the trigger. value is varchar(10) and it can be any one of the following values.

Important: The First and Last triggers must be two different triggers.

Value	Description
First	Trigger is fired first.
Last	Trigger is fired last.
None	Trigger is fired in undefined order.

[ @stmttype= ] 'statement_type'

Specifies the SQL statement that fires the trigger. statement_type is varchar(50) and can be INSERT, UPDATE, DELETE, LOGON, or any Transact-SQL statement event listed in DDL Events. Event groups cannot be specified.

A trigger can be designated as the First or Last trigger for a statement type only after that trigger has been defined as a trigger for that statement type. For example, trigger TR1 can be designated First for INSERT on table T1 if TR1 is defined as an INSERT trigger. The Database Engine returns an error if TR1, which has been defined only as an INSERT trigger, is set as a First, or Last, trigger for an UPDATE statement. For more information, see the Remarks section.

@namespace= { 'DATABASE' | 'SERVER' | NULL }

When triggername is a DDL trigger, @namespace specifies whether triggername was created with database scope or server scope. If triggername is a logon trigger, SERVER must be specified. For more information about DDL trigger scope, see DDL Triggers. If not specified, or if NULL is specified, triggername is a DML trigger.

DML Triggers

There can be only one First and one Last trigger for each statement on a single table. +

If a First trigger is already defined on the table, database, or server, you cannot designate a new trigger as First for the same table, database, or server for the same statement_type. This restriction also applies Last triggers.

Replication automatically generates a first trigger for any table that is included in an immediate updating or queued updating subscription. Replication requires that its trigger be the first trigger. Replication raises an error when you try to include a table with a first trigger in an immediate updating or queued updating subscription. If you try to make a trigger a first trigger after a table has been included in a subscription, sp_settriggerorder returns an error. If you use ALTER TRIGGER on the replication trigger, or use sp_settriggerorder to change the replication trigger to a Last or None trigger, the subscription does not function correctly.

DDL Triggers

If a DDL trigger with database scope and a DDL trigger with server scope exist on the same event, you can specify that both triggers be a First trigger or a Last trigger. However, server-scoped triggers always fire first. In general, the order of execution of DDL triggers that exist on the same event is as follows:

1. The server-level trigger marked First.

2. Other server-level triggers.

3. The server-level trigger marked Last.

4. The database-level trigger marked First.

5. Other database-level triggers.

6. The database-level trigger marked Last.

General Trigger Considerations

If an ALTER TRIGGER statement changes a first or last trigger, the First or Last attribute originally set on the trigger is dropped, and the value is replaced by None. The order value must be reset by using sp_settriggerorder.

If the same trigger must be designated as the first or last order for more than one statement type, sp_settriggerorder must be executed for each statement type. Also, the trigger must be first defined for a statement type before it can be designated as the First or Last trigger to fire for that statement type.

Permissions

To set the order of a DDL trigger with server scope (created ON ALL SERVER) or a logon trigger requires CONTROL SERVER permission.

To set the order of a DDL trigger with database scope (created ON DATABASE) requires ALTER ANY DATABASE DDL TRIGGER permission.

To set the order of a DML trigger requires ALTER permission on the table or view on which the trigger is defined.

EXAMPLES

EXAMPLE 1:

sp_settriggerorder @triggername = 'TR_Employee_Update', @order='First', @stmttype = 'UPDATE';

EXAMPLE 2:

sp_settriggerorder @triggername = 'TR_Employee_Salary_Update', @order='Last', @stmttype = 'None';

EXAMPLE 3:

sp_settriggerorder @triggername = 'TR_Employee_Bonus_Update', @order='Last', @stmttype = 'UPDATE';

EXAMPLE 4:

sp_settriggerorder @triggername = 'TR_Database_Log', @order='First', @stmttype = 'ALTER_TABLE', @namespace = 'DATABASE';

Execution Order of SQL Trigger

The following query provides the execution order of SQL trigger.

SELECT

sys.TABLES.name,

sys.TRIGGERS.name,

sys.TRIGGER_EVENTS.type,

sys.TRIGGER_EVENTS.TYPE_DESC,

IS_FIRST,

IS_LAST,

sys.TRIGGERS.CREATE_DATE,

sys.TRIGGERS.MODIFY_DATE

FROM sys.TRIGGERS

INNER JOIN sys.TRIGGER_EVENTS

ON sys.TRIGGER_EVENTS.object_id = sys.TRIGGERS.object_id

INNER JOIN sys.TABLES

ON sys.TABLES.object_id = sys.TRIGGERS.PARENT_ID

WHERE sys.TABLES.name = 'Employee'

ORDER BY MODIFY_DATE

SQL Tutorials

Navigation

SQL Trigger

A SQL Trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server.

CREATE SQL Trigger

ALTER SQL Trigger

Example for modifying the existing Trigger using the ALTER TRIGGER command is given below:

EXECUTE SQL Trigger

DROP SQL Trigger

Example for deleting the existing SQL trigger using the DROP TRIGGER command is given below:

RENAME SQL Trigger

DISABLE SQL Trigger

ENABLE SQL Trigger

Query the list of SQL Trigger

ENABLE / DISABLE SQL Trigger

Execution Order of SQL Trigger

[ @triggername= ] '[ triggerschema.] triggername'

[ @order= ] 'value'

Is the setting for the new order of the trigger. value is varchar(10) and it can be any one of the following values.

Important: The First and Last triggers must be two different triggers.

[ @stmttype= ] 'statement_type'

Specifies the SQL statement that fires the trigger. statement_type is varchar(50) and can be INSERT, UPDATE, DELETE, LOGON, or any Transact-SQL statement event listed in DDL Events. Event groups cannot be specified.

@namespace= { 'DATABASE' | 'SERVER' | NULL }

DML Triggers

DDL Triggers

General Trigger Considerations

Permissions

Execution Order of SQL Trigger

The following query provides the execution order of SQL trigger.

No comments:

Post a Comment

Pages

Pages

Pages