Iwant to post all changes of records of table copied into a separate table
CREATE TABLE dbo.mytable(
field1 numeric(13,2) NOT NULL,
field2 char(4) NOT NULL,
field3 char(4) NOT NULL,
field4 varchar(30) NOT NULL,
field5 datetime NULL,
field6 datetime NULL)
This is my table.
I want to create a separate table, which will record, all changes, as and when changes to the above table..
CREATE TABLE dbo.mytable_audit(
field1 numeric(13,2) NOT NULL,
field2 char(4) NOT NULL,
field3 char(4) NOT NULL,
field4 varchar(30) NOT NULL,
field5 datetime NULL,
field6 datetime NULL,
idactivity numaric),
iddatestamp datetime)
I want to capture changes of first table into 2nd table (who did insert/update/delete on this table with time stamp).
There are Insert, Update, Delete and Instead of Triggers. You will want to create an Insert, Update and Delete triggers for your purpose.
Here's a short example:
create trigger mytable_del on mytable
for delete
as
declare @stuff
insert something into mytable_audit
where something and something else
end
go
Here's the documentation. If you have problems with specific parts of your triggers, then you can post those specific code questions here, but it's unlikely that someone here will write your triggers for you.