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

Question

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.

Answer 1

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