Programming in MS SQL Server 2012

ALTER Trigger



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

-- Example 134 --

 

USE EBusiness

GO

     

ALTER TRIGGER dbo.DMLProductTableTrigger ON dbo.Product

AFTER INSERT, UPDATE

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 and update records into Product table. System occurs warning message when execute the following Transact-SQL statement based on invalid product expiry date.

-- Example 135 --

 

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

 

UPDATE Product SET

Exp_Date = '12-31-2010'

WHERE Prod_Code = 'TP01'


Query Output Screen



* * * * *


Email Your Comment To AUTHOR