TRIGGER FOR UPDATE

[Harish]

I HAVE TWO TABLES IN THE DATABSE T1 AND THE SECOND TABLE(T2) IS FOR AUDITING.
THE STRUCTURE OF BOTH THE TABLES IS SIMILAR WITH ONE MORE COLUMN 'STATUS' IN THE T2.
I HAVE ALREADY WRITTEN THE TRIGGERS FOR THE INSERT AND DELETE TRIGGERS SO THAT I CAN SAVE THE
RECORD WITH STATUS OF 'NEW INSERT' OR 'DELETE' OR 'UPDATE' IN THE AUDIT TABLE(T2).
HELP ME IN WRITING THE TRIGGER FOR UPDATE.
MY PROBLEM IS I DON'T KNOW HOW TO STORE THE UPDATED COLUMNS VALUES INTO VARIABLES AND SO THAT
I CAN STORE THE OLD VALUES INTO THE AUDIT TABLE(T2).
I M USING SQL SERVER 6.5, SO WE CAN NOT USE COLUMNS_UPDATED() IN THE TRIGGER.
PLEASE SUGGEST ME THE CODE SO THAT I CAN STORE THE OLD ROW IN THE TABLE(T1) TO TABLE (T2)
WITH STATUS 'UPDATE' IN THE T2.

PLEASE SUGGEST ME..ITS URGENT.

THANKS IN ADVANCE
HARISH




=============================
/*test trigger for insert status */
if exists (select * from sysobjects where id = object_id('dbo.tri_t1_insert&#39 and sysstat & 0xf = 8)
drop trigger dbo.tri_t1_insert
GO

CREATE TRIGGER tri_t1_insert ON dbo.t1
FOR INSERT
AS
declare @v1 binary(20),
@v2 varchar(255)
Begin
select @v1=stamp,@v2=name from inserted
insert into t2(stamp,name,status) values(@v1,@v2,'NEW INSERT&#39
end
GO
========================================
/*test trigger for delete status */
if exists (select * from sysobjects where id = object_id('dbo.tri_t1_delete&#39 and sysstat & 0xf = 8)
drop trigger dbo.tri_t1_delete
GO

CREATE TRIGGER tri_t1_delete ON dbo.t1
FOR delete
AS
declare @v1 binary(20),
@v2 varchar(255)
Begin
select @v1=stamp,@v2=name from deleted
insert into t2(stamp,name,status) values(@v1,@v2,'DELETE&#39

end

[Ivo]

Hi, Harish
old values are stored in the table deleted, so code in your update trigger would look :

CREATE TRIGGER tri_t1_update ON dbo.t1
FOR update
AS
Begin
INSERT T2 (stamp, name, status) SELECT stamp, name, 'UPDATED' FROM deleted
end


------------
Harish at 8/20/01 12:19:16 PM

I HAVE TWO TABLES IN THE DATABSE T1 AND THE SECOND TABLE(T2) IS FOR AUDITING.
THE STRUCTURE OF BOTH THE TABLES IS SIMILAR WITH ONE MORE COLUMN 'STATUS' IN THE T2.
I HAVE ALREADY WRITTEN THE TRIGGERS FOR THE INSERT AND DELETE TRIGGERS SO THAT I CAN SAVE THE
RECORD WITH STATUS OF 'NEW INSERT' OR 'DELETE' OR 'UPDATE' IN THE AUDIT TABLE(T2).
HELP ME IN WRITING THE TRIGGER FOR UPDATE.
MY PROBLEM IS I DON'T KNOW HOW TO STORE THE UPDATED COLUMNS VALUES INTO VARIABLES AND SO THAT
I CAN STORE THE OLD VALUES INTO THE AUDIT TABLE(T2).
I M USING SQL SERVER 6.5, SO WE CAN NOT USE COLUMNS_UPDATED() IN THE TRIGGER.
PLEASE SUGGEST ME THE CODE SO THAT I CAN STORE THE OLD ROW IN THE TABLE(T1) TO TABLE (T2)
WITH STATUS 'UPDATE' IN THE T2.

PLEASE SUGGEST ME..ITS URGENT.

THANKS IN ADVANCE
HARISH




=============================
/*test trigger for insert status */
if exists (select * from sysobjects where id = object_id('dbo.tri_t1_insert&#39 and sysstat & 0xf = 8)
drop trigger dbo.tri_t1_insert
GO

CREATE TRIGGER tri_t1_insert ON dbo.t1
FOR INSERT
AS
declare @v1 binary(20),
@v2 varchar(255)
Begin
select @v1=stamp,@v2=name from inserted
insert into t2(stamp,name,status) values(@v1,@v2,'NEW INSERT&#39
end
GO
========================================
/*test trigger for delete status */
if exists (select * from sysobjects where id = object_id('dbo.tri_t1_delete&#39 and sysstat & 0xf = 8)
drop trigger dbo.tri_t1_delete
GO

CREATE TRIGGER tri_t1_delete ON dbo.t1
FOR delete
AS
declare @v1 binary(20),
@v2 varchar(255)
Begin
select @v1=stamp,@v2=name from deleted
insert into t2(stamp,name,status) values(@v1,@v2,'DELETE&#39

end