Help with multiple row trigger

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

.NET Inside SQL Server (2005)

Help with multiple row trigger

Author

Topic

sth_Weird
Starting Member

38 Posts

Posted - 2009-07-09 : 04:09:16

Hi,
I hope I'm in the right sub-forum, I wasn't sure where triggers belong to...sorry if I'm wrong.
I searched the forum for "multible row trigger" but nothing showed up so I hope this hasn't been asked before.
And also sorry if my grammar isn't the best but I'm not a native speaker

But now let's head to my actual question.
I need a trigger that handles multible rows. I've found some examples on google but none of them fit to what I need.
That's what the trigger should do if there was only one row:
- check if a certain column in another table (table2) is null. If so, a new row has to be inserted in a third table. The new id of that table has to be written back in table 2.
Although I'm rather new and unexperienced to triggers I've already managed to get this trigger to work.
But of course it only works for one row.
It is also possible that the trigger is for multible rows. It has to perform the actions above for each row. I need some kind of a for-loop but I don't know how this works. Can somebody help me please?
Thank you!

sth_Weird

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-09 : 04:13:08

can you show us your trigger code ?

KH
[spoiler]Time is always against us[/spoiler]

sth_Weird
Starting Member

38 Posts

Posted - 2009-07-09 : 04:33:09

Below I have a trigger of another table that is a bit easier than my actual one but it faces the same problem when it comes to multible rows so I guess it's easier to understand. If caused by a one-row-update, it checkes if a certain column (InformationID) in the table is "-1" and if it is, it gets a new id (using a function) and inserts a new row in another table, and writes the new id back into the table. In case of this trigger I know it will never be fired for more than one row so the check is unnecessary, but in the real table multible rows updates/inserts are possible.

CREATE TRIGGER insertErrorTexts ON dbo.tErrorCodeGroups FOR INSERT, UPDATE
AS
BEGIN
	IF (SELECT Count(*) FROM inserted) > 1
		RETURN

	ELSE IF EXISTS (SELECT * FROM inserted WHERE (InformationID < 0))
		BEGIN
			DECLARE    @error		nvarchar(12),
			           @errorID 		bigint
		
			SELECT 	   @error = 'E_' + RTRIM(LTRIM(STR([ErrorCodeNumber]))), @errorID = [ErrorCodeID]
			FROM 		inserted 
		
			IF EXISTS (SELECT * FROM TextInformations WHERE GuiConstName LIKE RTRIM(LTRIM(@error)) AND NamespaceID = 2)
				RETURN
			ELSE
				BEGIN
					DECLARE 	@nextTextInfoID	bigint 
		
					SET		@nextTextInfoID = ([dbo].[GetNewTextInformationID]())
		
					INSERT INTO 	TextInformations (InformationID, GuiConstName, NamespaceID)
					VALUES 	(@nextTextInfoID, RTRIM(LTRIM(@error)) , 2)

                                        UPDATE tErrorCodeGroups 
					SET InformationID = @nextTextInfoID 
					WHERE ErrorCodeID = @errorID
		
				END
		END

END

sth_Weird

sth_Weird
Starting Member

38 Posts

Posted - 2009-07-09 : 05:43:24

sorry if I repost but sometimes when you edit an existing post other people who've read your post before don't notice the changes...
googleing on I'v stumbled upon cursors. they looked very interesing and I think they could solve my problem, but each article/post including cursor references were accompanied by warnings telling you they are evil and should not be used. They say most of the time there are alternatives to cursors. As I said, I'm still very new to triggers and stuff so I miss the knowledge and experience what is possible or not (so far I've only dealt with tables, queries (when it comes to them my skills are advanced) and very easy triggers...). Maybe you can tell me if cursors are the right way to go for me here, then I can try to get them to work. Or you think there is a workaround here, too, then I wouldn't have to waste my time on cursors.
Thanx
sth_Weird

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-09 : 07:05:20

try this


INSERT INTO 	TextInformations (InformationID, GuiConstName, NamespaceID)
SELECT [dbo].[GetNewTextInformationID](), 'E_' + RTRIM(LTRIM(STR(i.[ErrorCodeNumber])))
FROM   INSERTED i
WHERE  NOT EXISTS
       (
           SELECT *
           FROM   TextInformations x
           WHERE  NamespaceID  = 2
           AND    GuiConstName = 'E_' + RTRIM(LTRIM(STR([i.ErrorCodeNumber])))
       )

UPDATE e
SET    InformationID = t.InformationID
FROM   tErrorCodeGroups e
       INNER JOIN INSERTED i          ON  e.ErrorCodeID  = i.ErrorCodeID
       INNER JOIN TextInformations  t ON  t.GuiConstName = 'E_' + RTRIM(LTRIM(STR([i.ErrorCodeNumber])))
                                      AND t.NamespaceID  = 2

KH
[spoiler]Time is always against us[/spoiler]

sth_Weird
Starting Member

38 Posts

Posted - 2009-07-13 : 10:13:28

works. thanx!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources