question

tabularyee avatar image
tabularyee asked

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-2005sqltrigger
10 |1200

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

Fatherjack avatar image
Fatherjack answered
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 comment
10 |1200

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

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 Likes 0 ·
ThomasRushton avatar image
ThomasRushton answered
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 comments
10 |1200

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

A lovely read
0 Likes 0 ·
Oh, go on. They are a little bit magic. :)
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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