Variables possible in this trigger?

Question

question

tabularyee asked May 26, '11

Variables possible in this trigger?

Although this is completed successfully on completion, it is not having the desired update.

CREATE TRIGGER Trigger1
On dbo.[table1] 
FOR UPDATE
AS
Declare @Id int;
SELECT @Id = Issue_Id FROM dbo.[table1]

INSERT INTO dbo.[storage]
	SELECT Id, Title, project, Problem
	FROM dbo.[table2]
	WHERE Id = @Id

Is there something I am doing wrong or that I can't use variables within the scope of a trigger? Many thanks

sql-server-2005 sql trigger

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2011-05-26T03:07:43Z

Fatherjack answered May 26, '11

How many rows are there in [Table1]? If there are more than 1 then your assertion of @ID will be failing. you need to use TOP 1 with an ORDER BY and what about the other values in that table ...

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

tabularyee commented · May 26, 2011 at 03:24 AM

Yes it does, I was thinking that when I declared the variable it would be referencing the inserted Issue_id, which is the value that I need.

0 ·

Answer 2 · 2011-05-26T03:28:32Z

ThomasRushton answered May 26, '11

You'll be wanting to check the values in the "inserted" table - it's a (not-magic) table that contains the data values being inserted into your table. Have a look at for more info - check the samples down near the end. I say "not-magic", because of Read it and weep.

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

tabularyee commented · May 26, 2011 at 03:38 AM

A lovely read

0 ·

Fatherjack ♦♦ commented · May 26, 2011 at 07:45 AM

Oh, go on. They are a little bit magic. :)

0 ·

question