Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
is this a SQL Server login, or a login via some application? i.e. your checking if username & password (hash?) match & then granting access –  Nick Kavadias Dec 12 '13 at 5:38
    
its not sql server login. generally login into any table and values move into another table audit_login like transaction Date and status(success or failed) –  user31595 Dec 12 '13 at 6:06
1  
I think the best way is using the trigger as described below by Kin, otherwise even if you implement a stored proc, how do you want to call it? Trigger is the best option for this. –  Sky Dec 12 '13 at 22:38

1 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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.