Movian
01-09-2015, 08:22 AM
Hey so we have a problem where due to an unknown cause we have a) a table that sporadicaly (once every 2-4 weeks) is completly emptied... we are investigating this but are unable to reproduce so tracking it down is tricky... so for the moment we need to create triggers on the table so that all the data is copied to a second table on inserts and updates
I am not too familiar with triggers but the following makes sense in my head but am getting the error
Msg 213, Level 16, State 1, Procedure BackupPatient, Line 7
Column name or number of supplied values does not match table definition.
CREATE Trigger BackupTableDataOn MyTable
AFTER INSERT
AS
BEGIN
INSERT INTO MyTableBackup SELECT * FROM inserted
END
MyTableBackup is an exact duplicate of MyTable so they should have the same number of columns....
Once I have done this I will need to make a second trigger that updates the record with updates.
(but will not perform any delete or drop operations of any kind)
this way if the table is cleared we should be able to disable the trigger, copy from the backup table to the main table then activate the trigger again.
I am not too familiar with triggers but the following makes sense in my head but am getting the error
Msg 213, Level 16, State 1, Procedure BackupPatient, Line 7
Column name or number of supplied values does not match table definition.
CREATE Trigger BackupTableDataOn MyTable
AFTER INSERT
AS
BEGIN
INSERT INTO MyTableBackup SELECT * FROM inserted
END
MyTableBackup is an exact duplicate of MyTable so they should have the same number of columns....
Once I have done this I will need to make a second trigger that updates the record with updates.
(but will not perform any delete or drop operations of any kind)
this way if the table is cleared we should be able to disable the trigger, copy from the backup table to the main table then activate the trigger again.