SQL Trigger only fires once on multiple update

[supermatchgame]

I have written a trigger on a table that will fire whenever an insert happens. The trigger is set to insert the current date into a seperate table whenever the insert happens.

So if I insert 17 new rows into my trigger table, I would expect the trigger to fire 17 times and insert 17 rows with dates into the seperate table.

But this isn't happening. The messages section in the query analyser of sql 2005 says (1 row(s) affected) than (17 row(s) affected). All 17 inserts are there, but only one row with a data has been put in my other table.

Can someone explain where I've gone wrong?

 

[bborissov]

Triggers are fired ONLY once, after the whole job is done. That is why you must code the trigger and use INSERTED and DELETED meta tables to get all changed records. NEVER code trigger to handle just one record.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[supermatchgame]

Can anyone recommed an article that discusses how get access to and use the meta tables? I'm googling but not finding anything useful.

 

[bborissov]

These tables are accessible only in TRIGGER. They have exactly the same structure as the table where you made changes. In INSERTED table only inserted records are kept. In DELETED only deleted records are kept. And because when you change something in some records SQL SERVER do first DELETE and then INSERT new record, these changed records are in both tables, in DELETED you have a copy of the record BEFORE you made the change, in INSERTED table you have the same record but with new values in some (or all) fields. Can you post the code of the trigger just to try to change it to multiple records update.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[supermatchgame]

Well, the code that I wrote to create the trigger was:

CREATE TRIGGER [tgTest] ON [dbo].[LLPGAddresses]
FOR INSERT
AS
INSERT INTO TriggerResult (dtDate)
VALUES (getdate())

So do I have to query these metatables from inside the trigger when it is fired? How do I do that?

 

[ca8msm]

You can join your query to the INSERTED table - you should also have a look at this


------------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions

 

[bborissov]

Something like:

CREATE TRIGGER [tgTest] ON [dbo].[LLPGAddresses]
FOR INSERT
AS
INSERT INTO TriggerResult (dtDate)
SELECT GetDate() FROM INSERTED

(not tested)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[SQLDenis]

What is the point of this anyway?
You will have 17 identical getdate() values in that table

What is the purpose of this table? You don't seem to have any FK PK relationship

If it is to track the latest insert then 1 value is enough, why do you need 17


seems like a design issue

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions