October 18, 2012 at 5:15 am
These are two separate triggers though.
trDecrementBulkPurchases is called from "WHERE Transaction_Type = 'From Bulk Assignment'"
trPopulateBulkPurchases is called from "WHERE Transaction_Type = 'Bulk Purchase'"
I ran this also and no effect:
USE [TrackIT] ;
--GO
--EXEC sp_configure 'show advanced options', 1;
--GO
--RECONFIGURE ;
--GO
--EXEC sp_configure 'nested triggers', 1 ;
--GO
RECONFIGURE;
GO
October 18, 2012 at 6:30 am
briancampbellmcad (10/18/2012)
These are two separate triggers though.
Yeah, I know. If they were in one trigger it wouldn't be potentially a nested trigger problem.
Ok, you've given table definitions, now please some sample data and what exactly the data should look like after the insert.
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 18, 2012 at 7:42 am
INSERT INTO [TrackIT].[dbo].[tblTransactions]
([PO_Number],[Buyer_Initial],[Quantity_Remaining],[Quantity],[Software_Description]
,[HeatTicketNumber],[PO_Date],[Transaction_Date],[Transaction_Number]
,[Transaction_Type])
VALUES
('3321809','JDM',6195,6195,'SYMANTEC ENDPOINT ENCRYPTION FULL DISK',
'1581886','10/13/2011','10/13/2011',22,'Bulk Purchase')
GO
Should Produce this in the tblTransactions (which it does):
PO_Number 3321809
Buyer_Initial JDM
Quantity_Remaining 6195
Quantity 6195
Software_Description SYMANTEC ENDPOINT ENCRYPTION FULL DISK
HeatTicketNumber 1581886
PO_Date 10/13/2011
Transaction_Date 10/13/2011
Transaction_Number 22
Transaction_Type Bulk Purchase
And it should fire trigger "trPopulateBulkPurchases" writing the same data as above to tblBulkPurchases(which it does).
THEN:
INSERT INTO [TrackIT].[dbo].[tblTransactions]
([Buyer_Initial],[PO_Number],[Transaction_Number],[Transaction_Type])
VALUES
('JDM','3321809',23,'From Bulk Assignment')
GO
Should write this to tblTransactions (which it does):
Buyer_Initial JDM
PO_Number 3321809
Transaction_Number 23
Transaction_Type From Bulk Assignment
AND fire trigger "trDecrementBulkPurchases"
and the resulting UPDATE in the tblBulkPurchases shold look like this, only note that the quantity should now be 6194 BUT it is not decrementing so it remains 6195:
Quantity_Remaining 6195
October 18, 2012 at 7:46 am
Sample data please (as in example of what would be in the tables before that insert runs)
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 18, 2012 at 8:51 am
How should I paste the needed data? It becomes rather scrambled copying from SQL Server.
PO_NumberBuyer_InitialSoftware_IDQuantityQuantity_RemainingUnit_PriceSoftware_DescriptionAllocationAccountPurchaseAccountHeatTicketNumberPurchaseCostCenterPO_DateTransaction_DateTransaction_NumberTransaction_Type
3321809JDMNULL61956195NULLSYMANTEC ENDPOINT ENCRYPTION FULL DISKNULLNULL1581886NULL2011-10-132011-10-1322Bulk Purchase
5447441TRE405050315.22OfficePro1321353424244445242145154222012-05-122012-09-1517350Bulk Purchase
5465115OCC51200200120.20Apache3354825193219415995112542012012-05-122012-09-1517300Bulk Purchase
5965215OCC51100100120.20Apache3354825193219415995112452012012-05-122012-09-1517309Bulk Purchase
October 18, 2012 at 8:58 am
briancampbellmcad (10/18/2012)
How should I paste the needed data? It becomes rather scrambled copying from SQL Server.PO_NumberBuyer_InitialSoftware_IDQuantityQuantity_RemainingUnit_PriceSoftware_DescriptionAllocationAccountPurchaseAccountHeatTicketNumberPurchaseCostCenterPO_DateTransaction_DateTransaction_NumberTransaction_Type
3321809JDMNULL61956195NULLSYMANTEC ENDPOINT ENCRYPTION FULL DISKNULLNULL1581886NULL2011-10-132011-10-1322Bulk Purchase
5447441TRE405050315.22OfficePro1321353424244445242145154222012-05-122012-09-1517350Bulk Purchase
5465115OCC51200200120.20Apache3354825193219415995112542012012-05-122012-09-1517300Bulk Purchase
5965215OCC51100100120.20Apache3354825193219415995112452012012-05-122012-09-1517309Bulk Purchase
The best is to post an insert statement. The data needs to get into our test table so we can run the code.
_______________________________________________________________
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 18, 2012 at 9:10 am
Like this?:
INSERT INTO [TrackIT].[dbo].[tblTransactions]
([Buyer_Initial],[PO_Number],[Software_ID],[Transaction_Number],[Transaction_Type])
VALUES
('OCC','5465115','51',17999,'From Bulk Assignment')
GO
INSERT INTO [TrackIT].[dbo].[tblTransactions]
([PO_Number],[Buyer_Initial],[Software_ID],
[Quantity_Remaining],[Quantity],[Unit_Price],
[Software_Description],[AllocationAccount],[PurchaseAccount],
[HeatTicketNumber],[PurchaseCostCenter],[PO_Date],
[Transaction_Date],[Transaction_Number],[Transaction_Type])
VALUES
('5465115','OCC','51',100,100,'120.20','Apache','3354825','1932194',
'159951','1245201','5/12/2012','9/15/2012',17998,'Bulk Purchase')
GO
October 18, 2012 at 10:48 am
Your trigger is intended to update tblBulkPurchases. I don't have any data in that table. I can' test your trigger because the update has no rows to update.
_______________________________________________________________
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 18, 2012 at 10:59 am
The trigger that is fired upon inserting data to tblTransactions will also populate the table tblBulkPurchases:
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
October 18, 2012 at 11:31 am
Why don't you just have the one trigger that populates bulk transactions and then does the necessary updates? Honestly. I don't understand what you're trying to do here.
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 18, 2012 at 12:03 pm
It's a matter of timing - bulk purchases of software are handled in one transaction within the tblTransactions and may linger in inventory possibly for weeks before before each is assigned to a user. Each assignment of a software requires a separate transaction - thus a purchase of 200 copies requires 201 entries with only one being the movement of the product into inventory. In addition many copies of software are purchased and assigned one at a time - but this is another set of logic to come.
It is I'm sure possible to create one trigger based on CASE or IF-ELSE type structure to insert or update the tables based on the transaction type, and I would be eager to combine all if I can figure it out.
The trigger however that is failing to decrement the inventory, may have a flaw in its logic or the design of the two tables - possibly a problem with tblBulkPurchases having PO_Number as Primary Key while tblTransactions is keyed to Transaction_Number (?).
The flaw may show up in a combined trigger (?).
October 19, 2012 at 7:07 am
Did I get the data you needed or other information for the testing? Just can't seem to find what could be wrong with this trigger...
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 19, 2012 at 7:42 am
briancampbellmcad (10/19/2012)
Did I get the data you needed or other information for the testing? Just can't seem to find what could be wrong with this trigger...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
I can't speak for Gail but at this point the details of your issue are so scattered across 50 or so posts that I can't get it straightened out. I know you have posted all the details but I just can't figure out what is what at this point. Can you post in a single post the ddl for the tables (I am a little unclear how many tables are involved at this point), then the one piece that I still have never seen is sample data for tblBulkPurchases. Your trigger is supposed to update that table but we don't have the row that it should be updating because the Transaction_Type is not the same value as the one being inserted from your trigger.
Honestly, it seems like the biggest challenge is that your data structures seems to be lacking consistency and that what you are trying to do is store a calculated value. Quantity_Remaining should be calculated when it is needed instead of trying to store that value on every row in a transaction table.
_______________________________________________________________
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 19, 2012 at 7:46 am
OK Got it working... may not be pretty, but thoroughly tested and it is decrementing the Quantity_Remaing from tblBulkPurchases!
Thanks all, I learned a lot about virtual tables that I would not have learned if you guys had not steared me in that direction...
CREATE TRIGGER [dbo].[trDecrementBulkPurchases]
ON [dbo].[tblTransactions]
AFTER INSERT
AS
BEGIN
IF (SELECT Transaction_Type FROM INSERTED) = 'From Bulk Assignment'
UPDATE tblBulkPurchases
SET [Quantity_Remaining] = [Quantity_Remaining] - 1
WHERE tblBulkPurchases.PO_Number in (SELECT PO_Number FROM INSERTED)
END
GO
October 19, 2012 at 7:57 am
From your original codes your 2nd insert won't insert into the [tblBulkPurchases] since the trans type is not "bulk purchase" so there's no record in the tblBulkPurchases with a "From Bulk Assignment" for your 2nd trigger to update.
The trigger is firing but there's no matching record to update in the tblBulkPurchases table since it's looking for trans type "From Bulk Assignment" and you're never inserting that into tblBulkPurchases. The logic is slightly flawed.
GO
ALTER 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
INSERT INTO [dbo].[tblTransactions]
([PO_Number],[Buyer_Initial],[Quantity_Remaining],[Quantity],[Software_Description]
,[HeatTicketNumber],[PO_Date],[Transaction_Date],[Transaction_Number]
,[Transaction_Type])
VALUES
('3321809','JDM',6195,6195,'SYMANTEC ENDPOINT ENCRYPTION FULL DISK',
'1581886','10/13/2011','10/13/2011',22,'Bulk Purchase')
GO
select * from [tblTransactions]
select * from tblBulkPurchases
INSERT INTO [dbo].[tblTransactions]
([Buyer_Initial],[PO_Number],[Transaction_Number],[Transaction_Type])
VALUES
('JDM','3321809',23,'From Bulk Assignment')
GO
---------------------------------------------------------------
Mike Hahn - MCSomething someday:-)
Right way to ask for help!!
http://www.sqlservercentral.com/articles/Best+Practices/61537/
I post so I can see my avatar :hehe:
I want a personal webpage 😎
I want to win the lotto 😀
I want a gf like Tiffa :w00t: Oh wait I'm married!:-D
Viewing 15 posts - 31 through 45 (of 45 total)
You must be logged in to reply to this topic. Login to reply