Insert triggers updating an audit table

Question

Post reply

Insert triggers updating an audit table

ArtoisBB

SSC Rookie

Points: 34
More actions
April 28, 2014 at 4:43 am

#285001

Hi,
Apologies if this is in the incorrect forum.
I have created a trigger using the below code:
CREATE TRIGGER [TriggerName]
ON [InsertDataIntoThisTable]
AFTER INSERT
AS
UPDATE
[AuditTable]
SET LastUpdated = GETDATE()
WHERE ...
The number of rows being inserted into the table can vary from hundreds to several million. I was wondering if the update statement is only done once per batch of inserts (this is all I need) or for every row of inserted data?
Many thanks

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323546 More actions · Answer 1

once per batch.

you can get the count of records affected from the inserted table itself, if that's what you wanted to track:

SELECT COUNT(*) As RecordsAffected FROM INSERTED

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ArtoisBB SSC Rookie Points: 34 More actions · Answer 2

Thanks for the swift response Lowell.

The data is inserted using SSIS packages which state the number of rows that have gone in - thanks for the suggestion though.

Many thanks again.

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 3

For something like this, consider and "INSTEAD OF" trigger for the inserts. That way, you're only hitting the table once. Updating after the fact will prove to be a huge PITA with a huge amount comparatively huge amount of log file space being used not to mention a serious amount of overhead from the double-hit.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 4

Actually, since this is for INSERTs, consider putting a default on the Last_Updated column along with a NOT NULL constraint and you won't even need a trigger.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ArtoisBB SSC Rookie Points: 34 More actions · Answer 5

Hi Jeff,

Would the below be a better solution?

CREATE TRIGGER [TriggerName]

ON [InsertDataIntoThisTable]

INSTEAD OF INSERT

AS

BEGIN

DECLARE @Count INT

SELECT @Count = COUNT(*) FROM inserted

END

IF

@Count > 0

UPDATE

[AuditTable]

SET LastUpdated = GETDATE()

WHERE ...

Many thanks

Sean Lange SSC Guru Points: 286609 More actions · Answer 6

ArtoisBB (4/29/2014)
Hi Jeff,
Would the below be a better solution?
CREATE TRIGGER [TriggerName]
ON [InsertDataIntoThisTable]
INSTEAD OF INSERT
AS
BEGIN
DECLARE @Count INT
SELECT @Count = COUNT(*) FROM inserted
END
IF
@Count > 0
UPDATE
[AuditTable]
SET LastUpdated = GETDATE()
WHERE ...
Many thanks

I agree with Jeff here 100%. You don't need a trigger for this at all. Just add a NOT NULL constraint to LastUpdated and give it a default of getdate().

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

ArtoisBB SSC Rookie Points: 34 More actions · Answer 7

I don't think I have been very clear.

I have an audit table that I want to use in order to monitor when data was last inserted into certain tables. It looks something like this:

CREATE TABLE [dbo].[AuditTable]

(

[TableID] [int] IDENTITY(1,1) NOT NULL,

[TableName] [varchar](100) NULL,

[LastUpdated] [datetime] NULL

)

I have put the above mentioned triggers on each table to update the LastUpdated column in this Audit Table. So the WHERE clauses in the triggers are

WHERE TableID = Whatever that table's ID is.

I don't want to add a LastUpdated column to all of the tables in this AuditTable.

Many thanks

Lowell SSC Guru Points: 323546 More actions · Answer 8

as long as you bullet proofed the code to make sure all tables already exist, your trigger is pretty close to working, but it must be a FOR TRIGGER, not an instead of trigger.

i might consider adding the insert-if-not-exists to it as well, just because.

CREATE TRIGGER [TriggerName]

ON [InsertDataIntoThisTable]

FOR INSERT

AS

BEGIN

UPDATE [AuditTable]

SET LastUpdated = GETDATE()

WHERE TableName='InsertDataIntoThisTable'

INSERT INTO [AuditTable] (TableName,LastUpdated)

SELECT 'InsertDataIntoThisTable'

,GETDATE() WHERE NOT EXISTS(SELECT 1 FROM AuditTable WHERE name='InsertDataIntoThisTable')

END --TRIGGER

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Sean Lange SSC Guru Points: 286609 More actions · Answer 9

ArtoisBB (4/29/2014)
I don't think I have been very clear.
I have an audit table that I want to use in order to monitor when data was last inserted into certain tables. It looks something like this:
CREATE TABLE [dbo].[AuditTable]
(
[TableID] [int] IDENTITY(1,1) NOT NULL,
[TableName] [varchar](100) NULL,
[LastUpdated] [datetime] NULL
)
I have put the above mentioned triggers on each table to update the LastUpdated column in this Audit Table. So the WHERE clauses in the triggers are
WHERE TableID = Whatever that table's ID is.
I don't want to add a LastUpdated column to all of the tables in this AuditTable.
Many thanks

This seems a strange requirement but if you are going to do this I have a few suggestions to your structures here. First of all I would not use an identity. I would use an int and have it be the object_id of the table. There is no need to store the table name at all. Also, if the intention here is to store the datetime when a row was last inserted you should name your column LastInsertedDate instead of LastUpdated. Finally give your table a meaningful name instead of AuditTable. What are you auditing? What are you going to do if you want to create a second table for auditing? Certainly AuditTable2 is rather silly.

Here is how I would go about something like this.

create table InsertAudit

(

object_id int not null primary key clustered,

LastInsertedDate datetime not null

)

go

create table AuditTest

(

SomeValue varchar(20)

)

go

create trigger AuditTest_Insert

on AuditTest

after insert as

update InsertAudit

set LastInsertedDate = GETDATE()

where object_id = object_id('AuditTest')

if @@ROWCOUNT = 0

insert InsertAudit

select OBJECT_ID('AuditTest'), GETDATE()

go

--Now we need to insert some data into our table

insert AuditTest

select 'Here is some data'

select *, OBJECT_NAME(object_id) as TableName from InsertAudit

go

drop table AuditTest

drop table InsertAudit

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 10

ArtoisBB (4/29/2014)
I don't think I have been very clear.
I have an audit table that I want to use in order to monitor when data was last inserted into certain tables. It looks something like this:
CREATE TABLE [dbo].[AuditTable]
(
[TableID] [int] IDENTITY(1,1) NOT NULL,
[TableName] [varchar](100) NULL,
[LastUpdated] [datetime] NULL
)
I have put the above mentioned triggers on each table to update the LastUpdated column in this Audit Table. So the WHERE clauses in the triggers are
WHERE TableID = Whatever that table's ID is.
I don't want to add a LastUpdated column to all of the tables in this AuditTable.
Many thanks

I understand why someone might want to do such a thing with the capture of when a table was last updated although identifying the last column updated is a bit of a misnomer because updates can update many columns.

With that in mind, take a look at sys.dm_db_index_usage_stats dynamic management view. It may save you a lot of trouble.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)