Trigger In SQL

There are two types of Triggers :
. After Triggers

      . Instead of Triggers.

 .    After Triggers :

A. After triggers run after an insert,update or delete on a table.

     They are not supported for views.

      After triggers divided into 3 types.

 1. After Insert Trigger.

 2. After Update Trigger.

 3. After Delete Trigger

Magic Tables :

1. Magic tables are nothing but inserted and deleted which are temporary object created by server.

2. In magic tables server internally to hold the recently inserted values in the case of insert and to hold recently deleted values in the case of delete ,to hold before updating values or after updating values in the case of update.

There are two types of magic tables in SQL Server.

Inserted

Deleted

Following Example show how trigger works after executing.

Step 1 : Create table ' tblCustomer'

USE [myPractiseDB]

GO

/****** Object:  Table [dbo].[tblCustomer]    Script Date: 1/11/2017 11:53:55 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[tblCustomer](

       [CustomerId] [nchar](10) NULL,

       [CustomerName] [nvarchar](50) NULL

) ON [PRIMARY]

GO

Step 2: Create table ' tblAudit'

USE [myPractiseDB]

GO

/****** Object:  Table [dbo].[tblAudit]    Script Date: 1/11/2017 11:30:12 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[tblAudit](

       [LastUpdateTime] [datetime] NULL

) ON [PRIMARY]

GO

Step 3: Create Trigger 'triggerupdate'

create trigger triggerupdate

on tblCustomer

After insert ,delete,update

As

Begin

insert into tblAudit (LastUpdateTime) values (GETDATE())

End

Step 4: Record insert into the tblCustomer the trigger execute and updated record in tblAudit table show below screen.

After Adding new column like OldValue and NewValue in tblAudit then alter the trigger below code.

USE [myPractiseDB]

GO

/****** Object:  Trigger [dbo].[triggerupdate]    Script Date: 1/11/2017 11:31:47 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER trigger [dbo].[triggerupdate]

on [dbo].[tblCustomer]

After insert ,delete,update

As

Begin

declare @OldValue varchar(50)

declare @NewValue varchar(50)

--fetch the old values

select @OldValue=CustomerName from deleted

--fetch the new values

select @NewValue=CustomerName from inserted

insert into tblAudit (LastUpdateTime,OldCustomerName,NewCustomerName) values (GETDATE(),@OldValue,@NewValue)

End

Result show in the below screen:

1. Write a query to create a copy of a table using Transact-SQL-Command

   Select * into  TempUserMaster from UserMaster

2. How to find procedure and function update details .

   Select name, create_date, modify_date from sys.objects

   where type = 'P' or  name='Tbl_Folder'

   Select * from sys.objects;

   Select name, create_date, modify_date from sys.objects

   where type = 'fn' and name = 'Function_Name'

More Reading...!