Logging of Transaction details on a table

--   Script on Logging Transaction Detail on Master table  by using Trigger-
---  Author :: S SIVAPRASAD MCDBA 
---- Date   : 19 Apr 2003

------- Create the table
create table test1
(
  col1    SMALLINT IDENTITY,
  Tran_Dt  datetime default(getdate())
)


------- Create the Log table
create table test1_LOG
(
  col1    SMALLint,
  action  varchar(20) NULL,
  Tran_Dt  datetime default(getdate())
)
  

---- Create Trigger on test1 table 
-----Trigger to log the events & detail of Master table Test1 to Test_Log table
alter Trigger test1_trg
on test1
for insert,update,delete
as
begin ---<< BEGIN OF TRIGGER >>---

 set nocount on
 Declare @Tran_Status varchar(20)
  
   IF EXISTS (SELECT '*'  FROM DELETED)
  BEGIN                
IF EXISTS (SELECT '*'  FROM INSERTED)
                BEGIN
INSERT INTO test1_LOG(col1,Action)
        SELECT COL1,'UPDATE' 
                        FROM INSERTED
                END
ELSE    
                BEGIN
INSERT INTO test1_LOG(col1,Action)
        SELECT COL1,'DELETE' 
                        FROM DELETED
                END

  END        
ELSE 
                BEGIN
INSERT INTO test1_LOG(col1,Action)
        SELECT COL1,'INSERT' 
                        FROM INSERTED
                END

END  ---<< END OF TRIGGER >>---


-----##  I N S E R T  ## ----------

---- Insert the records in Master table
insert into test1 default values
go
insert into test1 default values
go
insert into test1 default values
go
insert into test1 default values
go
insert into test1 default values
go
insert into test1 default values
go
insert into test1 default values
go


select * from test1
/*-----  Result after Insert----------
col1   Tran_Dt                     
------ --------------------------- 
1      2003-04-19 16:45:28.660
2      2003-04-19 16:46:41.647
3      2003-04-19 16:46:48.707
4      2003-04-19 16:46:48.707
5      2003-04-19 16:46:48.717
6      2003-04-19 16:46:48.727
7      2003-04-19 16:46:48.737

(7 row(s) affected)

-----  Result after Insert----------*/
select * from test1_LOG
go
/*-----  Result after Insert----------
col1   action               Tran_Dt                     
------ -------------------- --------------------------- 
1      INSERT               2003-04-19 16:45:28.670
2      INSERT               2003-04-19 16:46:41.657
3      INSERT               2003-04-19 16:46:48.707
4      INSERT               2003-04-19 16:46:48.707
5      INSERT               2003-04-19 16:46:48.717
6      INSERT               2003-04-19 16:46:48.727
7      INSERT               2003-04-19 16:46:48.737

(7 row(s) affected)

-----  Result after Insert----------*/

-----##  D E L E  T E  ## ----------
DELETE FROM TEST1
WHERE COL1 = 3
GO
DELETE FROM TEST1
WHERE COL1 = 7
GO

select * from test1
GO
/*-----  Result after DELETE ON MASTER TABLE ----------
col1   Tran_Dt                     
------ --------------------------- 
1      2003-04-19 16:45:28.660
2      2003-04-19 16:46:41.647
4      2003-04-19 16:46:48.707
5      2003-04-19 16:46:48.717
6      2003-04-19 16:46:48.727

(5 row(s) affected)


-----  Result after DELETE----------*/
select * from test1_LOG
go
/*-----  Result after DELETE ON LOG TABLE----------
col1   action               Tran_Dt                     
------ -------------------- --------------------------- 
1      INSERT               2003-04-19 16:45:28.670
2      INSERT               2003-04-19 16:46:41.657
3      INSERT               2003-04-19 16:46:48.707
4      INSERT               2003-04-19 16:46:48.707
5      INSERT               2003-04-19 16:46:48.717
6      INSERT               2003-04-19 16:46:48.727
7      INSERT               2003-04-19 16:46:48.737
3      DELETE               2003-04-19 16:49:37.510
7      DELETE               2003-04-19 16:49:42.517


(9 row(s) affected)
-----  Result after Insert----------*/


-----##  U P D A T E  ## ----------
UPDATE TEST1
SET Tran_Dt = '01 JAN 1900'                    
WHERE COL1 = 4
GO

select * from test1
GO
/*-----  Result after UPDATE ON MASTER TABLE ----------
col1   Tran_Dt                     
------ --------------------------- 
1      2003-04-19 16:45:28.660
2      2003-04-19 16:46:41.647
4      1900-01-01 00:00:00.000
5      2003-04-19 16:46:48.717
6      2003-04-19 16:46:48.727

(5 row(s) affected)


-----  Result after UPDATE---------*/
select * from test1_LOG
go
/*-----  Result after UPDATE----------
col1   action               Tran_Dt                     
------ -------------------- --------------------------- 
1      INSERT               2003-04-19 16:45:28.670
2      INSERT               2003-04-19 16:46:41.657
3      INSERT               2003-04-19 16:46:48.707
4      INSERT               2003-04-19 16:46:48.707
5      INSERT               2003-04-19 16:46:48.717
6      INSERT               2003-04-19 16:46:48.727
7      INSERT               2003-04-19 16:46:48.737
3      DELETE               2003-04-19 16:49:37.510
7      DELETE               2003-04-19 16:49:42.517
4      UPDATE               2003-04-19 16:55:02.797

(10 row(s) affected)

-----  Result after UPDATE----------/