Triggers and set statements

[LeonAtRC]

When doing an update to several records at one time the following trigger will fire on only 1 record. Consequently I have to use a cursor to update each individual record separately.

ALTER TRIGGER Repl_Starts_Trigger1
ON dbo.Repl_Starts
		FOR UPDATE
	AS
	DECLARE @Pd integer
	DECLARE @Tm_G varchar(8)
	DECLARE @Tm_C varchar(8)
	SELECT @Pd = PID FROM INSERTED
	SELECT @Tm_G = GunStart FROM INSERTED
	SELECT @Tm_C = ChipStart FROM INSERTED
	EXEC usp_Starts @Pd=@Pd, @Tm_G=@Tm_G, @Tm_C=@Tm_C

Is there a way to update through a set statement that will fire the trigger for all records being updated?

Thanks
Leon

 

[mrdenny]

Nope, a cursor is required.

The only option is to within the trigger create a temp table, and load the needed values for all records from inserted into the temp table. Then have the stored procedure use the data in the temp table to do what ever it needs to.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[LeonAtRC]

Thanks Denny, I was afraid of that.
I like your idea and will use it elsewhere but for this project it would be impracticle.
Leon

 

[SQLSister]

The other option is to see what the sp you are calling does. It is possible you could replace the call to it with set-based code that deos the same thing. Reuse of code is not alawys a good idea, especially it it means you put a cursor on a trigger which can really slow down a system. Without knowing what the proc does, it is hard to say how difficult it would be to create code that does the same thing in a set-based fashion, but the possibility should be examined before using a cursor in a trigger.

Questions about posting. See faq183-874

 

[LeonAtRC]

Thank you SQLSister for your input.
The cursor won't be in a trigger. It will be in a stored procedure. The problem is that I'm transferring data from one database to another over a LAN. The target DB being the one with the trigger so the code inside the loop would be someting like:

UPDATE RemSvr.dbo.tbl set fld='xxx' where IDfld = 'yyy'

I just don't like the idea of opening a connection to a remote server for every iteration in the loop.
(This would be similiar to allowing triggers for bulk transfer.)

 

[SQLSister]

The trigger must be written to handle multiple values on insert, update or delete (depending on the kind of trigger) You cannot predict who else will eventually try to insert multiple records. Any trigger which only handles single records must be re-written or you will have data integrity problems. This is not an optional re-write.

Whether the cursor is in the sp or the trigger is irrevelant in terms of performance, it will be bad no matter what. The best thing to do is to not use the sp that requires only one value at a time sent to it, rewrite it to process multiple values. I know that is a pain, but it is far better than trying to determine why your system is always timing out.

Questions about posting. See faq183-874