SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Trigger help.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sapator
Constraint Violating Yak Guru

Greece
438 Posts

Posted - 09/22/2013 :  13:26:43  Show Profile  Reply with Quote
Being refreshing my sql knowledge. So for triggers.
SQL example.

USE AdventureWorks2012;
GO
IF OBJECT_ID ('Purchasing.LowCredit','TR') IS NOT NULL
   DROP TRIGGER Purchasing.LowCredit;
GO
-- 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 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


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?
Thanks.

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 09/22/2013 :  13:40:17  Show Profile  Reply with Quote
Its an internal temporary table available within the Trigger. It will contain details of data which is currently getting inserted into the main table PurchaseOrderHeader. It assumes the structure same as the table on which trigger is defined.
There's also a similar table called DELETED available which will contain deleted (old) information. It will not have any role in insert though. It will have deleted details in DELETE operation and old data in case of UPDATE operation (which is performed as DELETE + INSERT operation internally)


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

sapator
Constraint Violating Yak Guru

Greece
438 Posts

Posted - 09/22/2013 :  18:25:41  Show Profile  Reply with Quote
Oh, so it's an internal table. MS examples insert that table and assume that we know that is an internal used table.
Thank you for that.
P.S.
Nevermind. I was asking for update table but i've read your answer again and you have stated so.

Edited by - sapator on 09/22/2013 18:26:58
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 09/23/2013 :  12:23:04  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000