Sql-server – Move values from one table to another one via stored procedure in sql server 2008

insertsql-server-2008stored-procedures

I have two tables

  1. Login (userid, username, password)
  2. audit_login (userid, Transaction_Date, Status (success or failed)

Now I want create a stored procedure in which, if anyone wants to login then their userid, the Transaction_Date and Status (success or failed) details should automatically come into another table audit_login.

I will then want to know how many times a login was successful.

Best Answer

You can easily implement your requirement using a trigger on Login table that will log info in the audit_login table.

the key is using table trigger to log info in the audit table for auditing.

I will see if I can provide a working code. But you got the idea now.

Update: below is a prototype code that will help you.

Note: you dont need any Stored procedure, A trigger will do the job.

You can put try/catch to handle errors, which I have not done as this is only to help you understand the concept.

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Logins]') AND type in (N'U'))
DROP TABLE [dbo].[Logins]
GO
create table Logins (userid varchar(15) not null
                        ,username varchar(max)
                        ,[password] varchar(50)
                        ,[status] varchar(1)) -- S = Success, F= Fail
go

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[audit_login]') AND type in (N'U'))
DROP TABLE [dbo].[audit_login]
GO
SELECT [userid],[username],[status]  
    ,AuditDataState=CONVERT(VARCHAR(10),'')   
    ,AuditDMLAction=CONVERT(VARCHAR(10),'')    
    ,AuditUser =CONVERT(SYSNAME,'')  
    ,AuditDateTime=CONVERT(DATETIME,'01-JAN-1900')  
    into dbo.audit_login  
FROM dbo.Logins  
WHERE 1=2 

go                      
CREATE TRIGGER Logins_Insert 
ON dbo.Logins  
FOR INSERT  
AS  

 INSERT INTO dbo.audit_login  
 SELECT [userid],[username],[status],'New','Insert',SUSER_SNAME(),getdate()  FROM INSERTED   

GO  
CREATE TRIGGER Logins_Delete  
ON dbo.Logins  
FOR DELETE  
AS  

 INSERT INTO dbo.audit_login  
 SELECT [userid],[username],[status],'Old','Delete',SUSER_SNAME(),getdate()  FROM DELETED   
GO

CREATE TRIGGER Logins_Update  
ON dbo.Logins  
FOR UPDATE  
AS  

 INSERT INTO dbo.audit_login  
 SELECT [userid],[username],[status],'New','Update',SUSER_SNAME(),getdate()  FROM INSERTED   

 INSERT INTO dbo.audit_login  
 SELECT [userid],[username],[status],'Old','Update',SUSER_SNAME(),getdate()  FROM DELETED   

GO

Now insert some data :

-- Insert New Data
insert into Logins
values ('kin001', 'kin','$tr0ng01','s') 

select * from audit_login

-- Update the current Data
update Logins
set userid = 'kin-up-01'
where userid = 'kin001'

select * from audit_login

-- delete the data
delete from Logins
where userid = 'kin-up-01'

Below will be the end result:

Image

Related Question