How to update multi rows using triggers? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to update multi rows using triggers?

Author

Topic

nandithab
Starting Member

6 Posts

Posted - 2008-02-11 : 04:24:47

I have a trigger
to update multiple rows.But the trigger will fire only on the last row that is being updated .I need to make a trigger that fires for ALL the records that are being updated into a particular table.
For example:

Table BOOK:
id -PK for BOOK table
author
title
year

Table MODIFIEDBOOK
id-FK to MODIFIEDBOOK table
action

On updating the PK of Parent table ie.,BOOK,it has to update the FK of child table MODIFIEDBOOK with the corresponding updated id.

The trigger which i created on BOOK table,checks for any update that is performed on the BOOK table and it updates the child table MODIFIEDBOOK with the respective new id s.

But the trigger fires only for the last updated value in Parent Table Book.
I want that to fire for every updated row in parent table...

How can i do this?.

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2008-02-11 : 04:32:51

No, the trigger will make available all rows affected by the triggering action via the psuedotables.
Insert from either pseudotable in your update trigger.

Mark

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-11 : 04:38:55

How do you know that trigger is firing only for last row? It should fire after each update action on the table.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-11 : 04:45:01

[code]CREATE TRIGGER YourTrigger
ON BOOKS
AFTER UPDATE
AS
BEGIN
INSERT INTO MODIFIEDBOOK(id,action)
SELECT i.id,'U'
FROM INSERTED i
END
GO[/code]

Please note that if you execute this script
update BOOK set year = '2000' where id > 1000.
trigger will fire only for 2000 rows (1001 to 3000) which got updated.

steffen
Starting Member

3 Posts

Posted - 2008-02-11 : 10:54:06

Could you post your trigger code?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources