April 28, 2014 at 4:43 am
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
April 28, 2014 at 5:06 am
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
April 28, 2014 at 5:12 am
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.
April 28, 2014 at 8:43 am
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
Change is inevitable... Change for the better is not.
April 28, 2014 at 8:44 am
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
Change is inevitable... Change for the better is not.
April 29, 2014 at 3:52 am
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
April 29, 2014 at 7:26 am
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/
April 29, 2014 at 12:10 pm
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
April 29, 2014 at 12:28 pm
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
April 29, 2014 at 12:31 pm
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/
April 29, 2014 at 4:36 pm
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
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply