October 17, 2012 at 8:52 am
"Start by using the INSERTED table (which will have exactly the same columns as the table the
trigger is on) to identify what rows were affected by the insert"
I'm assuming these lines below are OK so far per your (?):
CREATE TRIGGER [dbo].[trDecrementBulkPurchases]
ON [dbo].[tblTransactions] -- the trigger is on an INSERT event in the table "tblTransactions"
UPDATE [TrackIT].[dbo].[tblBulkPurchases] -- I'm updating values in the table "tblBulkPurchases"
............
WHERE Transaction_Type = 'From Bulk Assignment' -- a possible value looked for in "tblTransactions"
October 17, 2012 at 9:09 am
typical INSERT:
INSERT INTO [TrackIT].[dbo].[tblTransactions]
([Buyer_Initial],[PO_Number],[Software_ID],[Transaction_Number],[Transaction_Type])
VALUES
('OCC','5465115','51',17999,'From Bulk Assignment')
GO
the above should trigger a decrement by 1 of the value in [tblBulkPurchases].[Quantity_Remaining]:
UPDATE [TrackIT].[dbo].[tblBulkPurchases]
SET [Quantity_Remaining] = [Quantity_Remaining] - 1
and determine which record in to UPDATE in [tblBulkPurchases] based on a match on "PO_Number" between [tblBulkPurchases] and [tblTransactions]
SET [Quantity_Remaining] = [Quantity_Remaining] - 1
October 17, 2012 at 9:13 am
briancampbellmcad (10/17/2012)
"Start by using the INSERTED table (which will have exactly the same columns as the table thetrigger is on) to identify what rows were affected by the insert"
I'm assuming these lines below are OK so far per your (?):
CREATE TRIGGER [dbo].[trDecrementBulkPurchases]
ON [dbo].[tblTransactions] -- the trigger is on an INSERT event in the table "tblTransactions"
UPDATE [TrackIT].[dbo].[tblBulkPurchases] -- I'm updating values in the table "tblBulkPurchases"
............
WHERE Transaction_Type = 'From Bulk Assignment' -- a possible value looked for in "tblTransactions"
We seem to be at somewhat of an impasse here. We continue to tell you to read the documentation on triggers but it seems you have not. We have told you to use the INSERTED pseudo table in your trigger, you have not. We have asked for sample data, still don't have any. We have asked for an explanation of what you want your trigger to do, don't have it.
Here is the documentation about INSERTED and DELETED tables. http://msdn.microsoft.com/en-us/library/ms191300%28v=sql.105%29.aspx
Again, the code I posted way back at the beginning is VERY close to what you want. Read the article then review the code I posted.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 17, 2012 at 10:06 am
briancampbellmcad (10/17/2012)
I'm assuming these lines below are OK so far per your (?):CREATE TRIGGER [dbo].[trDecrementBulkPurchases]
ON [dbo].[tblTransactions] -- the trigger is on an INSERT event in the table "tblTransactions"
UPDATE [TrackIT].[dbo].[tblBulkPurchases] -- I'm updating values in the table "tblBulkPurchases"
............
WHERE Transaction_Type = 'From Bulk Assignment' -- a possible value looked for in "tblTransactions"
No, they are not.
You are not in any way restricting the rows updated to the ones related to the insert statement that just ran. Now, if you want to update all the rows in the table regardless of when they were inserted (now, last week, 6 months ago, etc), then you could do use what you have there, but triggers usually operate based on the rows that were affected by the insert that fired the trigger. To identify those rows, you need to join to or filter based on the table named INSERTED, which you still are not.
Please do some reading on triggers.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 17, 2012 at 12:23 pm
Really I appreciate challenging me as that is the way to learn 🙂 - I read the article and I am trying to apply it. I understand the concept of INSERTED as in the below (Oracle, which I have more exposure to, has not unsimilar contructs). So this example I have as a trigger uses INSERTED, but I am having a difficult time applying the logic to the current trigger:
USE [TrackIT]
GO
CREATE TRIGGER [dbo].[trPopulateBulkPurchases]
ON [dbo].[tblTransactions]
AFTER INSERT
AS
BEGIN
INSERT INTO tblBulkPurchases
(tblBulkPurchases.PO_Number,
tblBulkPurchases.Buyer_Initial,
tblBulkPurchases.Quantity,
tblBulkPurchases.Transaction_Number,
tblBulkPurchases.Quantity_Remaining,
tblBulkPurchases.Unit_Price,
tblBulkPurchases.Software_Description,
tblBulkPurchases.Software_ID,
tblBulkPurchases.PO_Date,
tblBulkPurchases.PurchaseCostCenter,
tblBulkPurchases.HeatTicketNumber,
tblBulkPurchases.PurchaseAccount,
tblBulkPurchases.AllocationAccount,
tblBulkPurchases.Transaction_Date,
tblBulkPurchases.Transaction_Type)
SELECT
INSERTED.PO_Number,
INSERTED.Buyer_Initial,
INSERTED.Quantity,
INSERTED.Transaction_Number,
INSERTED.Quantity_Remaining,
INSERTED.Unit_Price,
INSERTED.Software_Description,
INSERTED.Software_ID,
INSERTED.PO_Date,
INSERTED.PurchaseCostCenter,
INSERTED.HeatTicketNumber,
INSERTED.PurchaseAccount,
INSERTED.AllocationAccount,
INSERTED.Transaction_Date,
INSERTED.Transaction_Type
FROM INSERTED
WHERE Transaction_Type = 'Bulk Purchase'
END
GO
And then the problem trigger with an attempt to use INSERTED:
USE [TrackIT]
GO
CREATE TRIGGER [dbo].[trDecrementBulkPurchases]
ON [dbo].[tblTransactions]
AFTER INSERT
AS
BEGIN
UPDATE tblBulkPurchases
SET [Quantity_Remaining] = [Quantity_Remaining] - 1
WHERE Transaction_Type = 'From Bulk Assignment'
and tblBulkPurchases.PO_Number in (SELECT PO_Number FROM INSERTED)
END
GO
October 17, 2012 at 12:30 pm
Why are you inserting the data again to the same table? You have effectively created an endless loop. There is no need to insert the data to the same table as the trigger.
--EDIT--
I misread the trigger. It is on a different table.
The second part looks like it is probably correct.
Is it doing what you want now?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 17, 2012 at 12:34 pm
briancampbellmcad (10/17/2012)
CREATE TRIGGER [dbo].[trDecrementBulkPurchases]ON [dbo].[tblTransactions]
AFTER INSERT
AS
BEGIN
UPDATE tblBulkPurchases
SET [Quantity_Remaining] = [Quantity_Remaining] - 1
WHERE Transaction_Type = 'From Bulk Assignment'
and tblBulkPurchases.PO_Number in (SELECT PO_Number FROM INSERTED)
END
GO
That looks better, as long as PO_NUMBER is a primary/unique key.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 17, 2012 at 12:43 pm
Maybe explaining the relationships above would help... the first trigger populates the tblBulkPurchases via entries to the tblTransactions IF it is a 'Bulk Purchase' of a specific product say 100 copies of MS-Office... the second trigger is to PULL in essence from that inventory in tblBulkPurchases when another transaction is made in the tblTransactions e.g. ONE copy at a time is allocated of say MS-Office, thus the need to decrement the PO_Number's 'Remaining Quantity by 1. the first trigger works flawlessly... the second trigger show no errors, but it simply is not updating my tblBulkPurchases with the needed decrements of 1.
October 17, 2012 at 12:49 pm
in the DDL's I have Transaction_Number as the primary unique key, but the PO_Number can be repeated and is not unique:
CONSTRAINT [PK_dbo.tblTransactions] PRIMARY KEY CLUSTERED
([Transaction_Number]
in the blkPurchases table PO_Number is the key and is unique:
CONSTRAINT [PK_dbo.tblBulkPurchases] PRIMARY KEY CLUSTERED
([PO_Number] ASC
should I have a secondary non-unique key on PO_Number in tblTransactions?
On the DML side I can run this anonymous block and it writes to tblTransactions but fails to update tblBulkPurchases:
INSERT INTO [TrackIT].[dbo].[tblTransactions]
([Buyer_Initial],[PO_Number],[Software_ID]
,[Transaction_Number],[Transaction_Type])
VALUES
('FTS','5447441','51',17542,'From Bulk Assignment')
GO
October 17, 2012 at 1:04 pm
Define 'fails'. Throws an error, does nothing, converts the server into a black hole which sucks down the entire server room, something else?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 17, 2012 at 1:07 pm
It just doesn't update the tblBulkPurchases:
UPDATE tblBulkPurchases
SET [Quantity_Remaining] = [Quantity_Remaining] - 1
🙂 - no black holes, just stomach ulsers so far!
October 17, 2012 at 1:29 pm
Does this work?
UPDATE TBP
SET TBP.[Quantity_Remaining] = TBP.[Quantity_Remaining] - 1
FROM INSERTED I
INNER JOIN [tblTransactions] TT ON I.[Transaction_Number] = TT.[Transaction_Number]
INNER JOIN [tblBulkPurchases] TBP ON TT.[Transaction_Number] = TBP.[Transaction_Number]
WHERE TT.Transaction_Type = 'From Bulk Assignment'
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
October 17, 2012 at 1:36 pm
Do you have nested triggers enabled?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 17, 2012 at 1:43 pm
I ran this with system sp's:
USE [TrackIT] ;
GO
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE ;
GO
EXEC sp_configure 'nested triggers', 0 ;
GO
RECONFIGURE;
GO
October 18, 2012 at 1:57 am
That's probably why. The first trigger does the insert into Bulk Purchases, but since nested triggers aren't on, the trigger on that table won't fire.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 16 through 30 (of 45 total)
You must be logged in to reply to this topic. Login to reply