Programming in MS SQL Server 2012

CREATE Trigger



The following example creates new DML trigger in EBusiness database. This trigger prints a user-defined message to database or application user, when tries to INSERT a record with invalid product expiry date in Product table.

-- Example 130 --

 

USE EBusiness

GO

 

IF OBJECT_ID ('dbo.DMLProductTableTrigger','TR') IS NOT NULL

   DROP TRIGGER PREM dbo.DMLProductTableTrigger;

GO

 

CREATE TRIGGER dbo.DMLProductTableTrigger ON dbo.Product

AFTER INSERT

AS

      IF EXISTS

           (

           SELECT * FROM dbo.Product WHERE Exp_Date <= GETDATE()

           )

      BEGIN

           RAISERROR ('The Product Expiry Date is less than or equal to System Date', 16, 1)

           ROLLBACK TRANSACTION

           RETURN

      END

GO


Query Output Screen



The following Transact-SQL statements are attempts to insert records into Product table.

System occur warning message when execute third Transact-SQL statement.

-- Example 131 --

 

INSERT INTO Product VALUES ('TP01','Product TP A',12345,'12-31-2014',15,'Remark TP A')

 

INSERT INTO Product VALUES ('TP02','Product TP B',12345,'12-31-2014',15,'Remark TP B')

 

INSERT INTO Product VALUES ('TP03','Product TP C',12345,'12-31-2010',15,'Remark TP C')

 

INSERT INTO Product VALUES ('TP04','Product TP D',12345,'12-31-2014',15,'Remark TP D')

 

INSERT INTO Product VALUES ('TP05','Product TP E',12345,'12-31-2014',15,'Remark TP E')


Query Output Screen



The following example creates new DDL trigger in EBusiness database. This trigger prevents to drop synonym or table object from database and try to avoid table structure modification.

-- Example 132 --

 

USE EBusiness

GO

 

IF EXISTS (SELECT * FROM sys.triggers

    WHERE parent_class = 0 AND name = 'DDLControlTrigger')

      DROP TRIGGER DDLControlTrigger ON DATABASE;

GO

 

CREATE TRIGGER DDLControlTrigger

ON DATABASE

FOR DROP_SYNONYM, DROP_TABLE, ALTER_TABLE

AS

   RAISERROR ('You must disable Trigger "DDLControlTrigger" to drop synonyms!',10, 1)

   ROLLBACK

GO


Query Output Screen



The following LOGON trigger describes to denies an attempt to log in to SQL Server as a member of the login_test login if there are already three user sessions running under that login.

-- Example 133 --

 

USE master;

GO

 

CREATE LOGIN login_test

           WITH PASSWORD = '3KHJ6dhx(0xVYsdf'

           MUST_CHANGE,

           CHECK_EXPIRATION = ON;

GO

 

GRANT VIEW SERVER STATE TO login_test

GO

 

CREATE TRIGGER connection_limit_trigger

ON ALL SERVER WITH EXECUTE AS 'login_test'

FOR LOGON

AS

BEGIN

IF ORIGINAL_LOGIN()= 'login_test' AND

(SELECT COUNT(*) FROM sys.dm_exec_sessions

WHERE is_user_process = 1 AND

            original_login_name = 'login_test') > 3

      ROLLBACK

END


Query Output Screen



* * * * *


Email Your Comment To AUTHOR