There are two types of Triggers :
. After Triggers
. After Triggers
. Instead of Triggers.
. After 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'
