Being refreshing my sql knowledge. So for triggers.
IF OBJECT_ID ('Purchasing.LowCredit','TR') IS NOT NULL
DROP TRIGGER Purchasing.LowCredit;
-- 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
IF EXISTS (SELECT *
FROM Purchasing.PurchaseOrderHeader p
JOIN inserted AS i
ON p.PurchaseOrderID = i.PurchaseOrderID
JOIN Purchasing.Vendor AS v
ON v.BusinessEntityID = p.VendorID
WHERE v.CreditRating = 5
RAISERROR ('A vendor''s credit rating is too low to accept new
purchase orders.', 16, 1);
-- 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)
JOIN inserted AS i ? This is not to be found in the database i have (latest AdventureWorks2012).
Or is it a trigger based statement?
So the trigger will check for CreditRating = 5 on all rows(?).
Can i determine the specific row(s) that is inserted instead?