October 3, 2003 at 9:13 am
I have a detail table which uses a sequence number ( int, IDENTITY ) as a key. I created a trigger on the table to write the sequence number to another table whenever a record is updated. I am selecting the sequence number as Select @seq from ( select SeqNbr deom DELETED ). The results show the sequence number as NULL. Any help on why?
October 3, 2003 at 9:30 am
The syntax of your select statement looks a little strange. I presume this is just a typo?
Do you check the value of @@rowcount at the start of the trigger?
If the update statement caused no rows to be updated, the trigger will still fire, but you will have no rows in the deleted table.
October 3, 2003 at 11:19 am
What does it mean Select @seq from ( select SeqNbr deom DELETED ).
It is incorrect.
You have to type:
select @seq = SeqNbr from DELETED
But do not forget that you may delete several rows so beware what you will get assigned to @seq (in this case the SeqNbr value of the last row selected)
Bye
Gabor
Bye
Gabor
October 3, 2003 at 11:24 am
If you are trying to populate into another table the seqn number, select the seqn number directly from the deleted table into the table to which you are inserting.
insert into table1(seqn)
select seqn from Deleted.
October 3, 2003 at 3:45 pm
The @@ROWCOUNT suggestion did the trick!
The NEW statement is:
DECLARE @SEQ int
IF @@ROWCOUNT <> 0
BEGIN
SELECT @SEQ = (SELECT SeqNbr FROM DELETED)
INSERT INTO tblActions(action_type, seqnbr) VALUES('U', @seq)
END
October 3, 2003 at 4:29 pm
quote:
The @@ROWCOUNT suggestion did the trick!The NEW statement is:
DECLARE @SEQ int
IF @@ROWCOUNT <> 0
BEGIN
SELECT @SEQ = (SELECT SeqNbr FROM DELETED)
INSERT INTO tblActions(action_type, seqnbr) VALUES('U', @seq)
END
Don't do this. If multiple rows are updated within one statement, you'll get an error and the update will roll back because you cannot set a local variable to more than one value. Use the method suggested by charlieo:
INSERT tblActions(Action_Type, SeqNbr)
SELECT 'U', SeqNbr FROM deleted
--Jonathan
--Jonathan
October 8, 2003 at 3:29 am
If you have more than one updates taking place at the same time on the table then u will get the error, so simply insert the values from the deleted table in the other table.
Cheers!
Arvind
Arvind
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply