Re: Trigger

From: me (jscott56_at_teranews.com)
Date: 08/01/04 

Date: Sun, 1 Aug 2004 00:39:49 -0400

The usual way of doing this is to create a new table, having all the same
columns as the original, plus one more column that is a timestamp column.
On the primary key, if it is an identity column on the original, DONT also
make it an identity on the new table.

Name the new table with a name that is equal to the oriignal + 'History' or
something like that.

Ok, now create 2 triggers on the original table, base one on the Insert
event and the other the Update event.

The content of the insert trigger will be

Insert into abc_History(a,b,c,d,e)
Select * from inserted

The content of the insert trigger will be

Insert into abc_History(a,b,c,d,e)
Select * from deleted

that should do it.

Now, any time
"AQ Mahomed" <aq786@shoecrazy.co.za> wrote in message
news:%23pw32eWcEHA.2388@TK2MSFTNGP11.phx.gbl...
> Hi
>
> I need to record all changes to my current table automatically to a new
> table.
>
> Is this possible with a trigger.
>
> Many Thanks in advance
>
> AQ
>
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Relevant Pages

  • Re: AFTER INSERT?
    ... If you have an identity column you can really reset the value from within a ... is there a After Insert trigger functionality in SQL Server 2000? ... > and reset it if it has passed a certain limit. ... > It appears that I cannot do this WHILE I am inserting, ...
    (microsoft.public.sqlserver.programming)
  • Re: trigger???
    ... If all you want is an ascending integer key, use an identity column. ... If not, you CAN use a 'before' trigger, called an 'instead of' trigger in ... create table MyTable (MyTableID int not null primary key, ...
    (microsoft.public.sqlserver.server)
  • Re: @@IDENTITY in SQL server
    ... sent through to the Execute statement of a connection object), ... guaranteed to get the identity column of the record that was just ... If the table into which you are inserting has a trigger that inserts data ...
    (microsoft.public.inetserver.asp.db)
  • insert trigger problem
    ... identity column as a primary key -> every time i add a value using my form i ... primary key and with using a trigger this variable gets changed. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Logon auditing through trigger on sysprocesses?
    ... The change logging part is working, with a simple trigger. ... The 'TestLog' table must exist before the trigger is added, ... In the real test I included an identity column, ...
    (microsoft.public.sqlserver.msde)