September 1, 2011 at 2:05 pm
Since the 'inserted' and 'deleted' record sets include all records touched by an INSERT, UPDATE or DELETE, I'm assuming that could determine which actions where performed on which records by simply joining the recordsets accordingly? Is this reliable?
(off the top of my head)
INSERTED RECORDS
SELECT * FROM inserted WHERE [PrimaryKey] NOT IN (Select [PrimaryKey] from deleted)
UPDATED RECORDS
SELECT * FROM inserted WHERE [PrimaryKey] IN (Select [PrimaryKey] from deleted)
DELETED RECORDS
SELECT * FROM deleted WHERE [PrimaryKey] NOT IN (Select [PrimaryKey] from inserted)
Side Note:
Is it faster to use [IN/NOT IN] or to do a JOIN?
September 1, 2011 at 2:10 pm
david.holley (9/1/2011)
Since the 'inserted' and 'deleted' record sets include all records touched by an INSERT, UPDATE or DELETE, I'm assuming that could determine which actions where performed on which records by simply joining the recordsets accordingly? Is this reliable?(off the top of my head)
INSERTED RECORDS
SELECT * FROM inserted WHERE [PrimaryKey] NOT IN (Select [PrimaryKey] from deleted)
UPDATED RECORDS
SELECT * FROM inserted WHERE [PrimaryKey] IN (Select [PrimaryKey] from deleted)
DELETED RECORDS
SELECT * FROM deleted WHERE [PrimaryKey] NOT IN (Select [PrimaryKey] from inserted)
Side Note:
Is it faster to use [IN/NOT IN] or to do a JOIN?
When inserting, there is nothing in deleted.
when deleting, there is nothing in inserted.
the only time both tables will have records is during an update.
What I don't understand is what you are trying to accomplish. The where clause in all of your queries are redundant.
_______________________________________________________________
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/
September 1, 2011 at 2:11 pm
Take a look at Gail's blog for a great explanation and in depth look at join vs IN.
http://sqlinthewild.co.za/index.php/2010/01/12/in-vs-inner-join/[/url]
_______________________________________________________________
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/
September 1, 2011 at 2:19 pm
Sean Lange (9/1/2011)
david.holley (9/1/2011)
Since the 'inserted' and 'deleted' record sets include all records touched by an INSERT, UPDATE or DELETE, I'm assuming that could determine which actions where performed on which records by simply joining the recordsets accordingly? Is this reliable?(off the top of my head)
INSERTED RECORDS
SELECT * FROM inserted WHERE [PrimaryKey] NOT IN (Select [PrimaryKey] from deleted)
UPDATED RECORDS
SELECT * FROM inserted WHERE [PrimaryKey] IN (Select [PrimaryKey] from deleted)
DELETED RECORDS
SELECT * FROM deleted WHERE [PrimaryKey] NOT IN (Select [PrimaryKey] from inserted)
Side Note:
Is it faster to use [IN/NOT IN] or to do a JOIN?
When inserting, there is nothing in deleted.
when deleting, there is nothing in inserted.
the only time both tables will have records is during an update.
What I don't understand is what you are trying to accomplish. The where clause in all of your queries are redundant.
I'm in the process of building some change tracking into my DB. My users have a specific need to see a high level view of the action that was taken with the ability to see the specifics (Action, ProjectNumber). I'm going with an approach where a trigger on the table will create a record in a ChangeLog table that summarizies the action and the ProjectNumber (with some additional information). Hence, I needed to be able to determine which Projects were added, which were updated and which were deleted. By joining the tables together, it should thus be possible to determine the specific actions since inserted would contain inserted/updates with deleted containing deletes/updates.
The trigger runs AFTER the action hence the desire for flexibility in the general design.
September 1, 2011 at 2:34 pm
You can mix all the logic together in a single trigger and check the existence of records or you can create triggers for each action. Here is an example of an all-in-one type trigger.
create table TriggerTest
(
SomeValue varchar(25)
)
go
create trigger tr_TriggerTest on TriggerTest for INSERT, UPDATE, DELETE
as begin
if exists(select * from inserted, deleted)
begin
--this is an update
select 'update'
end
else if exists(select * from deleted)
begin
--this is a delete
select 'delete'
end
else
begin
--this is an insert
select 'insert'
end
end
go
select * from TriggerTest
insert TriggerTest select 'MyValue'
update TriggerTest set SomeValue = 'asdf'
delete triggertest
_______________________________________________________________
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/
September 1, 2011 at 2:44 pm
It'll be more along the lines of this...
INSERT INTO
ProjectInformationChangeLog
(Action, Element, WindowsUserId, DateTimeStamp, SQLServerTable, ShowNumber)
SELECT ShowNumber,'Delete', 'General Information', SYSTEM_USER, SYSDATETIME(), OBJECT_NAME(@@PROCID) From deleted GROUP BY ShowNumber WHERE ProjectNumber In (Select ProjectNumber from inserted)
INSERT INTO
ProjectHeaders_Audit
(ValueType,BatchNumber,ProjectNumber,Office,...)(SELECT 'Delete','0',BatchNumber,ProjectNumber,Office,... from deleted WHERE ProjectNumber NOT IN (SELECT ProjectNumber From Inserted))
Its rough as I'm still tweeking the trigger. The first INSERT creates the record in the ChangeLog - one for each project that was deleted. The second captures the deleted record in the audit log. It'll all be repeated for INSERTED records and then again for UPDATED records.
The triger will do
September 1, 2011 at 3:00 pm
david.holley (9/1/2011)
It'll be more along the lines of this...
INSERT INTO
ProjectInformationChangeLog
(Action, Element, WindowsUserId, DateTimeStamp, SQLServerTable, ShowNumber)
SELECT ShowNumber,'Delete', 'General Information', SYSTEM_USER, SYSDATETIME(), OBJECT_NAME(@@PROCID) From deleted GROUP BY ShowNumber WHERE ProjectNumber In (Select ProjectNumber from inserted)
INSERT INTO
ProjectHeaders_Audit
(ValueType,BatchNumber,ProjectNumber,Office,...)(SELECT 'Delete','0',BatchNumber,ProjectNumber,Office,... from deleted WHERE ProjectNumber NOT IN (SELECT ProjectNumber From Inserted))
Its rough as I'm still tweeking the trigger. The first INSERT creates the record in the ChangeLog - one for each project that was deleted. The second captures the deleted record in the audit log. It'll all be repeated for INSERTED records and then again for UPDATED records.
The triger will do
Not sure what you mean that the first insert creates a record for each project that was deleted. That will only find matches on an update but the hardcoded value and your description make it sound like a delete. Remember that the inserted table will be empty on a delete.
The skeleton I put together was to show you place holders of where your custom logic would go for each dml event.
The two queries you put together are close. Your first query could modify the where clause.
WHERE not exists (Select ProjectNumber from inserted)
This would mean the inserted table is empty and there it HAS to be a delete.
The second query...if there are records in deleted and inserted the primary key WILL ALWAYS be in both tables. In other words the second query as coded would only find anything when deleting from the base table.
_______________________________________________________________
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/
September 1, 2011 at 4:54 pm
The example that I posted is only the section that handles the Deletes, another section will handle Inserts with another handling Edits. The trigger is designed to work AFTER INSERT, EDIT, DELETE which is why all three scenarios are included. I should have it up and running by the weekend and will post the actual samples.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply