-- 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

-- 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')

-- 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

-- 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