-
Notifications
You must be signed in to change notification settings - Fork 0
/
An introduction to TRIGGERS in SQL Server.txt
77 lines (57 loc) · 2.92 KB
/
An introduction to TRIGGERS in SQL Server.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
An introduction to TRIGGERS in SQL Server
There are four main types of triggers these being DML triggers, DDL triggers, CLR(common runtime language) triggers and Logon triggers.
Data Definition Language (DDL) triggers fire in response to DDL events such as CREATE, ALTER and DROP (Table,View,Index,stored procedures.
DDL Events- https://msdn.microsoft.com/en-us/library/bb522542.aspx
DDL Triggers- https://docs.microsoft.com/en-us/sql/relational-databases/triggers/ddl-triggers#types-of-ddl-triggers
Server Scoped DDL Triggers
----Database scoped trigger will be located in the 'Database Triggers' folder under 'Programmability' of the database
CREATE TRIGGERE tr_DatabaseScopeTrigger
ON DATABASE
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE
AS
BEGIN
ROLLBACK
Print 'You can not create, alter or drop a table in the current database'
END
----Server scoped trigger must be at level ALL SERVER. This trigger will be found under the server in the 'Triggers' folder
CREATE TRIGGER tr_ServerScopeTrigger
ON ALL SERVER
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE
AS
BEGIN
ROLLBACK
Print 'You can not create, alter or drop a table in any database on the server'
END
To disable the Server Scope trigger use the following:
DISABLE TRIGGER tr_ServerScopeTrigger ON ALL SERVER
To enable
ENABLE TRIGGER tr_ServerScopeTrigger ON ALL SERVER
To remove it completely
DROP TRIGGER tr_ServerScopeTrigger ON ALL SERVER
EXECUTION ORDER OF TRIGGERS
Can an execution order be set for triggers? Yes
How do we set an execution order for triggers?
Server scope triggers will fire before database scope triggers.
Using the sp_settriggerorder stored procedure we can set the execution order of server scoped or database scoped triggers.
By default triggers will fire in the order in which they are created.
EXEC sp_settriggerorder
@triggername = 'tr_DatabaseScopeTrigger1',
@order='none',
@stmttype = 'CREATE_TABLE',---on what stmt in the trigger is this order to occur
@namespace= 'DATABASE'
GO
Where
@triggername is the name of the triger, @order is the order to execute, @stmttype is the type of statment in the trigger such as INSERT, DELETE, UPDATE or DDL event, @namespace is the scope of the trigger.
@namespace can have the values DATABASE, SERVER or NULL.
@order can have the values First, Last and None.
A trigger may be fired on more than one event as for example CREATE_TABLE and ALTER_TABLE. This is declared in the @stmttype parameter.
To summarize,
Server level triggers always fire before database level triggers.
Triggers are fired in the order in which they are created by default.
If the sp_settriggerorder stored procedure is used then the seqence will be:
1. the server scope trigger marked First
2. the server scope triggers marked none
3. the server scope trigger marked Last
4. the database scope trigger marked First
5. the database triggers marked none
6. the database trigger marked Last