trigger to write to two tables

Question

trigger to write to two tables

DaveBriCam

SSCarpal Tunnel

Points: 4298
More actions
October 16, 2012 at 5:58 am

#100130

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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

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

Lowell SSC Guru Points: 323518 More actions · Answer 2

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

DaveBriCam SSCarpal Tunnel Points: 4298 More actions · Answer 3

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.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

Remove the column qualifiers from the SELECT list, or replace them with "Inserted".

John

DaveBriCam SSCarpal Tunnel Points: 4298 More actions · Answer 5

Now it is working flawlessly by replacing the qualifiers with INSERTED. ... thanks all!