Translate

Friday, 22 May 2015

Sql Trigger Insert,Update,Delete

create table tblMain
  (
   Id int not null Primary Key identity(1,1) ,
   Name varchar(100),
   dflag int,
   userId varchar(20)
    )


create table tblMain_Image
  (
  AutoId bigint not null Primary key identity(1,1),
    Id int  not null,
    Name varchar(100),
    userId varchar(20),
    dflag int,
Sys_Date datetime default getdate(),
sys_Time  varchar(20) default RIGHT(CONVERT(CHAR(20), GETDATE(), 22), 11)--For AM and PM
    )
----------------------------------INSERT----------------------------  
create trigger [dbo].[Triger_Insert_tblMain] ON  [dbo].[tblMain] FOR INSERT
AS
BEGIN
     set nocount on
    insert into [dbo].[tblMain_Image]
           (Id,Name,userId,dflag)
    SELECT Id,Name,userId,0
    FROM inserted;
 
END
----------------------------------INSERT-----------------------------------


----------------------------------UPDATE---------------------------------
create trigger [dbo].[Triger_Update_tblMain] ON  [dbo].[tblMain] FOR UPDATE
AS
BEGIN
    set nocount on
    insert into [dbo].[tblMain_Image]
           (Id,Name,userId,dflag)
    SELECT Id,Name,userId,1
    FROM inserted;
 
END
---------------------------------UPDATE---------------------------------


---------------------------DELETE------------------------------------
create  trigger Triger_Delete_tblMain on  [dbo].[tblMain]
FOR DELETE
AS
begin
 set nocount on
     insert into [dbo].[tblMain_Image]
           (Id,Name,userId,dflag)
    SELECT Id,Name,userId,2
    FROM deleted;
end
---------------------------DELETE------------------------------------

No comments:

Post a Comment