Showing posts with label create a trigger in sql server. Show all posts
Showing posts with label create a trigger in sql server. Show all posts

Tuesday, August 27, 2013

Create a Trigger

Create a trigger that will insert the old and new Passwords into a Table.
Open SSMS--Select database[Master]-->Select tablename[Logins]-->Expand the table--> you can find the Triggers-->select that and right click to create New Trigger--> change the trigger as we need
In this example below am showing how can we store/insert the Password into Audit table[Old/New].
Audit is the new table with columns[OldPassword,NewPassword,DateTime].
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,K.Sai Krishna Yadav>
-- =============================================
CREATE TRIGGER AuditInfo 
   ON  Login
   AFTER INSERT,DELETE,UPDATE
AS 
BEGIN
declare @OldValue Varchar(50)
declare @NewValue Varchar(50)
select @OldValue=password from deleted
select @NewValue=password from inserted
Insert into Audit(AuditInfo,OldValue,NewValue) values (GETDATE(),@OldValue,@NewValue)
SET NOCOUNT ON;
    -- Insert statements for trigger here
END
GO
The above Trigger will fire when an INSERT,DELETE,UPDATE happens on Logins Table.