Using TRIGGER on an update

[Timcwilcox]

I have two tables TABLE1 and TABLE2, TABLE2 is a copy of TABLE1 and needs to be maintained.

I am using the trigger below

CREATE TRIGGER INSERT01 ON TABLE1
FOR INSERT
AS
INSERT INTO TABLE2
SELECT * FROM INSERTED
GO

CREATE TRIGGER DELETE01 ON TABLE1
FOR DELETE
AS
DELETE FROM TABLE2
SELECT * FROM DELETED
GO

The question is, without coding each and every line on the table, can i do an update using a similar rule to above.

There are no triggers on TABLE2.

Thanks in advance.

 

[fleckingerre]

Hi!

If you dont want to code every field, combine the delete and insert. First delete all updated records on table2 and after this make the insert:

CREATE TRIGGER UPDATE01 ON TABLE1
FOR UPDATE
AS
DELETE FROM TABLE2
SELECT * FROM INSERTED

INSERT INTO TABLE2
SELECT * FROM INSERTED
GO

Greetings
Remo

 

[vganeshbabu]

NOPE u cannot right a update statement without specifying the field list like Insert and Delete statement..

 

[Timcwilcox]

Thanks for this, however when the update trigger executes, it deletes all the rows from TABLE2 and then inserts the Updated row.

 

[MeanGreen]

Try this --- I used the code from Remo:

CREATE TRIGGER UPDATE01 ON TABLE1
FOR UPDATE
AS
DELETE FROM TABLE2
where table2.Primarykey = INSERTED.primarykey

INSERT INTO TABLE2
SELECT * FROM INSERTED
GO

Hope this helps.

 

[fleckingerre]

Hi Mean.

Yes, this normaly is the way I use such queries. Its just that some ppl like this SELECT. Actually I didnt test it, sorry.

Greetings
Remo

 

[Timcwilcox]

Thanks for your help guys, just starting out with MSQL and NT. Previous used UNIX+INFORMIX, but times are a changing.

I Think we can kill this thread now, as i have working triggers.