Implementing Triggers in SQL Server 2000
From the output we can conclude that when the user tries to insert data in the table USER_MASTER, three triggers are executed automatically. That is, you can write several AFTER triggers on one table for each of the three triggering actions.
Similarly, we can write several AFTER triggers on DELETE and UPDATE triggering actions.
Note: If a table has multiple AFTER triggers, then you can specify which trigger should be executed first and which trigger should be executed last using the stored procedure sp_settriggerorder. All the other triggers are in an undefined order which you cannot control.
An AFTER trigger can be created only on tables, not on views.
Using the script below, first we shall create a simple view [vwUserMaster] which will fetch the Username and Password from the table USER_MASTER.
Create View vwUserMaster
SELECT USERNAME, PASSWORD FROM USER_MASTER
CREATE TRIGGER trgOnView
Print (‘AFTER Trigger [trgOnView] – vwUserMaster !!’)
Server: Msg 208, Level 16, State 4, Procedure trgOnView, Line 2
Invalid object name ‘vwUserMaster’.
From the Output we can conclude that we cannot create an AFTER trigger on views.
Like stored procedures and views, triggers can also be encrypted. The trigger definition is then stored in an unreadable form. Once encrypted, the definition of the trigger cannot be decrypted and cannot be viewed by anyone, including the owner of the trigger or the system administrator.
CREATE TRIGGER trgEncrypted
ON User_Master WITH ENCRYPTION
Print (‘AFTER Trigger [trgEncrypted] Encrypted – Trigger executed !!’)
sysobjects.name AS [Trigger Name],
SUBSTRING(syscomments.text, 0, 26) AS [Trigger Definition],
OBJECT_NAME(sysobjects.parent_obj) AS [Table Name],
syscomments.encrypted AS [IsEncrpted]
sysobjects INNER JOIN syscomments ON sysobjects.id = syscomments.id
(sysobjects.xtype = ‘TR’)
Trigger Name Trigger Definition Table Name IsEncrpted
trgInsert CREATE TRIGGER trgInsert User_Master 0
trgInsert1 CREATE TRIGGER trgInsert1 User_Master 0
trgInsert2 CREATE TRIGGER trgInsert2 User_Master 0
trgEncrypted ??????????????? User_Master 1