May 2, 2002 at 11:46 am
I need to write a trigger to update the identity column in the 2nd table if a new row is added to the first table's identity column.
Here's what I have (doesn't work)
create table x (col1 numeric)
create table z (col1 numeric)
CREATE TRIGGER trigger_name
ON x
FOR INSERT, UPDATE
AS
If UPDATE(col1)
BEGIN
update z set col1= x.col1 from x
END
GO
insert into x values (6)
May 2, 2002 at 12:50 pm
Try
CREATE TRIGGER trigger_name
ON x
FOR INSERT, UPDATE
AS
If UPDATE(col1)
BEGIN
update z set col1= x.col1 from x
END
insert into x values (6)
The go ends the code completely.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 2, 2002 at 12:55 pm
This did not work. The 6 was inserted intot he first table but not the second.
-Kevin
May 2, 2002 at 1:06 pm
Not sure if this is what you want it to do...
create table x (col1 numeric)
go
create table z (col1 numeric)
go
CREATE TRIGGER trigger_name
ON x
FOR INSERT, UPDATE
AS
If UPDATE(col1)
BEGIN
update z set col1= (select i.col1 from inserted i, deleted d, z where z.col1 = d.col1)
END
GO
insert into x values (7)
go
insert into z values (7)
go
select * from z
go
update x set col1 = 8 where col1 = 7
go
select * from z
go
John Aspenleiter
John Aspenleiter
May 2, 2002 at 1:12 pm
Sorry, jaspenles sound right. I must have been trippin with that last one as I completely threw it wrong.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 2, 2002 at 1:14 pm
Simply put...
I have 1 table with an identity column.
Everytime that is incremented I need a second table's id column incremented also. I can make the second table's columnn an identity seed or I can ge the value of the seed from the first table.
-Kevin
May 2, 2002 at 1:24 pm
As table x is an identity column you cannot normally update it so this should handle the insert.
CREATE TRIGGER trigger_name
ON x
FOR INSERT
AS
INSERT INTO z (col1) SELECT col1 from inserted
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 2, 2002 at 1:52 pm
THANKS!!! Works great 🙂
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply