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

as

SELECT USERNAME, PASSWORD FROM USER_MASTER

GO

CREATE TRIGGER trgOnView

ON vwUserMaster

FOR INSERT

AS

BEGIN

        Print ('AFTER Trigger [trgOnView]  – vwUserMaster !!')

END

GO

Output

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

FOR INSERT 

AS

BEGIN

      Print ('AFTER Trigger [trgEncrypted] Encrypted  – Trigger executed !!')

END

GO

SELECT    

             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]

FROM

             sysobjects INNER JOIN syscomments ON sysobjects.id = syscomments.id

WHERE   

             (sysobjects.xtype = 'TR')

Output

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