Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- This trigger prevents a row from being inserted in the Purchasing.PurchaseOrderHeader
- -- table when the credit rating of the specified vendor is set to 5 (below average).
- CREATE TRIGGER Purchasing.LowCredit ON Purchasing.PurchaseOrderHeader
- AFTER INSERT
- AS
- IF EXISTS (SELECT *
- FROM Purchasing.PurchaseOrderHeader AS p
- JOIN inserted AS i
- ON p.PurchaseOrderID = i.PurchaseOrderID
- JOIN Purchasing.Vendor AS v
- ON v.BusinessEntityID = p.VendorID
- WHERE v.CreditRating = 5
- )
- BEGIN
- RAISERROR ('A vendor''s credit rating is too low to accept new
- purchase orders.', 16, 1);
- ROLLBACK TRANSACTION;
- RETURN
- END;
- GO
- -- This statement attempts to insert a row into the PurchaseOrderHeader table
- -- for a vendor that has a below average credit rating.
- -- The AFTER INSERT trigger is fired and the INSERT transaction is rolled back.
- INSERT INTO Purchasing.PurchaseOrderHeader (RevisionNumber, Status, EmployeeID,
- VendorID, ShipMethodID, OrderDate, ShipDate, SubTotal, TaxAmt, Freight)
- VALUES (
- 2
- ,3
- ,261
- ,1652
- ,4
- ,GETDATE()
- ,GETDATE()
- ,44594.55
- ,3567.564
- ,1114.8638 );
- GO
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement