Trigger Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Trigger Help

Author

Topic

sql_newbie121
Yak Posting Veteran

52 Posts

Posted - 2009-11-20 : 14:36:02

I have a customer table. I want to audit this table when any body inserts, updates or deletes records on this table from the application. I have a trigger but it does not seem working.
Whatever i do it should be recorded in the Audit_Customer table.
Please let me know if you are not clear on the question.

===============================================================

CREATE TABLE [Audit_Customer] (
TriggerType CHAR(1),
fieldID int,
TableName VARCHAR(128),
FieldName VARCHAR(128),
OldValue VARCHAR(1000),
NewValue VARCHAR(1000),
UpdateDate DATETIME,
UserName VARCHAR(128))
GO

CREATE TRIGGER Customer_Trigger
ON Customer FOR INSERT, UPDATE, DELETE
AS
BEGIN
SET NOCOUNT ON

DECLARE @bit INT ,
@fieldID INT ,
@char INT ,
@fieldname VARCHAR(128) ,
@TableName VARCHAR(128) ,
@sql VARCHAR(2000),
@UpdateDate VARCHAR(21) ,
@UserName VARCHAR(128) ,
@TriggerType CHAR(1)

SELECT @TableName = 'Customer'
-- Get Action Date and user
SELECT @UserName = SYSTEM_USER ,
@UpdateDate = CONVERT(VARCHAR(8), GETDATE(), 112) + ' ' + CONVERT(VARCHAR(12), GETDATE(), 114)

-- Action
IF exists (SELECT * FROM inserted)
IF exists (SELECT * FROM deleted)
SELECT @TriggerType = 'U'
ELSE
SELECT @TriggerType = 'I'
ELSE
SELECT @TriggerType = 'D'

-- Get the list of columns
SELECT * INTO #INSERT FROM inserted
SELECT * INTO #DELET FROM deleted
BEGIN
WHILE @fieldID <= (SELECT MAX(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS)

SELECT @fieldname = COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName and ORDINAL_POSITION = @fieldID
SELECT @sql = 'insert Audit_Customer
(TriggerType, TableName, FieldName, OldValue, NewValue, UpdateDate, UserName)'
SELECT @sql = @sql + ' select ''' + @TriggerType + ''''
SELECT @sql = @sql + ',''' + @TableName + ''''
SELECT @sql = @sql + ',''' + @FIELDNAME + ''''
SELECT @sql = @sql + ',CONVERT(VARCHAR(1000),d.' + @FIELDNAME + ')'
SELECT @sql = @sql + ',CONVERT(VARCHAR(1000),i.' + @FIELDNAME + ')'
SELECT @sql = @sql + ',''' + @UpdateDate + ''''
SELECT @sql = @sql + ',''' + @UserName + ''''
SELECT @sql = @sql + ' FROM #INSERT I FULL OUTER JOIN #DELET D'
SELECT @sql = @sql + ' where i.' + @fieldname + ' <> d.' + @FIELDNAME
SELECT @sql = @sql + ' or (i.' + @FIELDNAME + ' is null and d.' + @FIELDNAME + ' is not null)'
SELECT @sql = @sql + ' or (i.' + @FIELDNAME + ' is not null and d.' + @FIELDNAME + ' is null)'
END
EXEC (@sql)
END

GO

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-11-20 : 14:54:21

why not just make a copy of the ddl from the customer table, then add 5 fields: date, login, host, application, username.

Then your update/delete trigger simply inserts what is in the DELETED virtual table plus current_timestamp, SYSTEM_USER, host_name(), APP_NAME(), user_name().

Your insert trigger copies INSERTED plus those 5 in (if that's even necessary)

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-20 : 14:55:48

WHY are you using Dynamic SQL

WHY do you care about INSERTS?

WHY Are you going against the catalog, when a TRIGGEr is for a specific table?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

sql_newbie121
Yak Posting Veteran

52 Posts

Posted - 2009-11-20 : 15:05:07

CREATE TABLE [dbo].[Customer](
[Cust_ID] [int] NOT NULL IDENTITY(1,1),
[Cust_Last_Name] varchar(25) NULL,
[Cust_First_Name]varchar(25),
[Cust_address] varchar(50),
[cust_city] varchar(20),
cust_phone varchar(10),
update_date datetime,
login_name varchar(25),
host varchar(15),
application_name varchar(30)
) ON [PRIMARY]

Russell: I have this table and how can i write the trigger than? Thanks

sql_newbie121
Yak Posting Veteran

52 Posts

Posted - 2009-11-20 : 15:09:12

Brett:
WHY are you using Dynamic SQL------I thought i can get the results dynamically.

WHY do you care about INSERTS?---I do care because whoever enters the data from the application, if there are some issues, i can genrate the report who added what record on which date. See we had some issus on the data entry and if the same kind of things happen again, i can produce a result and find out who did the insertion.

WHY Are you going against the catalog, when a TRIGGEr is for a specific table?----I am going against system catalog because if i want to create the trigger i can just reuse it.

Let me know if you have any questions. I am learning from you Brett.

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-20 : 15:19:52

here

keep it simple

You code to generate the history tables and the triggers

Don't do that INSIDE the trigger

And the inserts are based on the base table...no need to keep a duplicate copy

http://weblogs.sqlteam.com/brettk/archive/2004/10/20/2242.aspx

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

sql_newbie121
Yak Posting Veteran

52 Posts

Posted - 2009-11-20 : 15:27:08

Brett:
I see what you mean and thanks for the link. How can i write the code to generate the history tables and triggers than? I can write generic trigger but the problem is i have to loop each column to see if there are new and old values in Inserted and Deleted virtual tables respectively. based on these inserted and deleted values i will be able to generate some kind of report. Thanks for your help.

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-20 : 15:37:33

cut and past the code to a sandbox data base and test it...you'll see what it does and it should be more clear

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

sql_newbie121
Yak Posting Veteran

52 Posts

Posted - 2009-11-20 : 16:15:29

I did but i did not understand what it did. I will look into it over the weekend and see if i can understand a littlebit better.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-11-20 : 16:51:24

looks like it generates triggers

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-20 : 17:46:51

have a good weekend

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam