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

ALTER TRIGGER dbo.DMLProductTableTrigger ON dbo.Product

AFTER INSERT, UPDATE

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

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

* * * * *

Your Comment To AUTHOR