Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

trigger to write to two tables Expand / Collapse
Author
Message
Posted Tuesday, October 16, 2012 5:58 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 13, 2014 2:33 PM
Points: 117, Visits: 315
Thanks in advance for any help with this!

I have a trigger that is successfully writing data to two different tables: tblTransactions & tblBulkPurchases... on the first run of an insert into tblTransactions I get a beautiful creation of the same in tblBulkPurchases... however on the second run of another insert into tblTransactions I get TWO identical records created in the tblBulkPurchases table... NOT GOOD! Is there a way of nesting or some other method to keep this from happening?

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_Date,
tblBulkPurchases.Transaction_Type)
SELECT
tblTransactions.PO_Number,
tblTransactions.Buyer_Initial,
tblTransactions.Quantity,
tblTransactions.Transaction_Date,
tblTransactions.Transaction_Type
FROM tblTransactions
WHERE Transaction_Type = 'Bulk Purchase'
END
GO
Post #1373178
Posted Tuesday, October 16, 2012 6:10 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:47 AM
Points: 5,233, Visits: 9,476
Please will you post DDL for the two tables, including any primary key or unique constraints. Please also give an example of an INSERT statement.

Are you sure that after every insert into one table, you want to copy everything into the other table, and not just the rows you have inserted?

John
Post #1373186
Posted Tuesday, October 16, 2012 6:20 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:22 PM
Points: 12,889, Visits: 31,839
the trigger is using the base table, isntead of just the new rows that exist in the special virtual tables INSERTED AND DELETED

this will insert just the new rows:
CREATE TRIGGER [dbo].[trPopulateBulkPurchases]
ON [dbo].[tblTransactions]
AFTER INSERT
AS
BEGIN
INSERT INTO tblBulkPurchases
(tblBulkPurchases.PO_Number,
tblBulkPurchases.Buyer_Initial,
tblBulkPurchases.Quantity,
tblBulkPurchases.Transaction_Date,
tblBulkPurchases.Transaction_Type)
SELECT
INSERTED.PO_Number,
INSERTED.Buyer_Initial,
INSERTED.Quantity,
INSERTED.Transaction_Date,
INSERTED.Transaction_Type
FROM INSERTED
WHERE INSERTED.Transaction_Type = 'Bulk Purchase'
END
GO

edited: fixed table aliases:


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1373191
Posted Tuesday, October 16, 2012 6:49 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 13, 2014 2:33 PM
Points: 117, Visits: 315
I'm getting the following error for each field of the tblTransactions when I try to execute the T-SQL to create the trigger:

Msg 4104, Level 16, State 1, Procedure trPopulateBulkPurchases, Line ...
The multi-part identifier "tblTransactions.Transaction_Type" could not be bound.
Post #1373207
Posted Tuesday, October 16, 2012 6:52 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:47 AM
Points: 5,233, Visits: 9,476
Remove the column qualifiers from the SELECT list, or replace them with "Inserted".

John
Post #1373210
Posted Tuesday, October 16, 2012 7:02 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 13, 2014 2:33 PM
Points: 117, Visits: 315
Now it is working flawlessly by replacing the qualifiers with INSERTED. ... thanks all!
Post #1373223
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse