July 21, 2008 at 12:37 pm
GSquared (7/21/2008)
hengert (7/21/2008)
I have to create a trigger for 9 tables. So, depending on how many columns each table has, it would be a nightmare to write.Unless you're talking about hundreds of columns per table (which is unusual), writing nine triggers should take less than half an hour.
I have to agree with this. It probably took you (and me) longer to write the creation procedure than it would have to write 2 or 3 actual triggers, that will perform better than the generic one.
Actually, though, you really don't need to log inserts. If the data is never updated or deleted, the actual row is the "log", and if it is updated or deleted, the "from" columns of the first logged action are the original inserted values.
I would have to agree with this as well.
If your log table has an ID column, that becomes your transaction ID. It includes all rows that were inserted/updated/deleted, in an easy-to-query XML format.
I don't necessarily agree that XML is easy to query, it is if you have XML experience, but it is still not as easy as straight T-SQL. I'd be interested in seeing your query though. I am working on a logging solution myself and like to have options.
G,
I tried your test code and got this error:
Msg 6819, Level 16, State 1, Line 31
The FOR XML clause is not allowed in a INSERT statement.
Any ideas why?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 21, 2008 at 12:48 pm
Yeah, I forgot about one detail. You have to assign the XML to an XML variable, then insert that. Otherwise, you get that error.
As far as querying XML goes, it's not that hard to learn. I'm looking at the trigger being generated by this auto-generic-code, and shuddering at the complexity of maintaining that.
What happens to it if a column is added to a table? Do you drop all the triggers and re-run the auto-generate script? Do you take the database offline while that's going on? Or do you try to manually modify that unreadable code? My eyes cross just looking at it, and I sight-read SQL pretty easily.
Plus look at all the nested IFs it has to go through, and the fact that it's RBAR (it's assigning "Old Value" and "New Value" one row at a time). My XML solution is far from perfect, but it works quite well, is easy to maintain, easy to read, and performs quite well.
To me, the time spent learning a little XML is worth the effort, if this is the other option.
Yeah, it's kind of fun coming up with something as complex as auto-generating these triggers. Kind of stimulates the mind, gets the creative juices flowing. But it's not very practical.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 21, 2008 at 12:55 pm
That is why I turned to this forum for answers.
My experience with this is very limited. I mainly only do Java code 😛 You can probably pick that up from the Procedure I wrote.
I wanted a genereic trigger since some of the tables we have, have more than 30 columns, but maybe that is not a concern then. I thought I had to write IF UPDATE(column_name) for each column.
July 21, 2008 at 1:01 pm
Yeah it definitely would be hard to maintain when you make schema changes.
I need to evaluate the logging I was planning, although I and my co-worker were going to log each table with a full record. The old copy record with a transaction date and a changed by field added to the table. I don't know that I want to go to a generic log table. It can grow pretty quickly which is why I like the customer table with a customer_log table.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 21, 2008 at 1:12 pm
I am not sure I understand this code at all. Is this just a template of some kind? Don't you need the IF UPDATE(column_name) ??
create table #Inserted (
ID int primary key,
Col1 varchar(100),
Col2 int)
create table #Deleted (
ID int primary key,
Col1 varchar(100),
Col2 int)
insert into #Inserted (ID, Col1, Col2)
select 1, 'x', 1 union all
select 2, 'y', 2
insert into #Deleted (ID, Col1, Col2)
select 2, 'z', 2 union all
select 3, 'w', 3
insert into dbo.LogTable (LoggedData)
select isnull(i.ID, d.ID) as ID,
i.Col1 as Col1_To, i.Col2 as Col2_To,
d.Col1 as Col1_From, d.Col2 as Col2_From,
case
when i.id is not null and d.id is not null then 'U'
when i.id is null and d.id is not null then 'D'
when i.id is not null and d.id is null then 'I'
end [Action]
from #inserted i
full outer join #deleted d
on i.id = d.id
for xml path
July 21, 2008 at 1:21 pm
No you do not because of the FULL OUTER JOIN between the inserted and deleted tables. Notice he is using the CASE statement to determine if it is an Insert, Update, Delete based on the data in inserted and deleted. The FULL OUTER JOIN will return all the rows in either table regardless of the existence of rows in the other. So on an insert you only get rows in the inserted table and on a delete you only get rows in the deleted table and the FULL OUTER JOIN handles that.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 21, 2008 at 2:09 pm
Here's a test you can run, that includes log-reading functions:
create function LogReader_Table1
(@XML_in XML)
returns @T1 table (
ID int,
Act char(1),
Col1_From varchar(100),
Col1_To varchar(100),
Col2_From varchar(100),
Col2_To int)
as
begin
insert into @T1 (ID, Act, Col1_From, Col1_To, Col2_From, Col2_To)
select
@xml_in.value('(i/@ID)[1]', 'int'),
@xml_in.value('(i/@Action)[1]', 'char(1)'),
@xml_in.value('(i/@Col1_From)[1]', 'varchar(100)'),
@xml_in.value('(i/@Col1_To)[1]', 'varchar(100)'),
@xml_in.value('(i/@Col2_From)[1]', 'varchar(100)'),
@xml_in.value('(i/@Col2_To)[1]', 'int')
return
end;
go
create function LogReader_Rows
(@XML_in XML)
returns table
as
return
(select t.c.query('.') as Result
from @xml_in.nodes('i') t(c));
go
create table #Log (
LogID int identity primary key,
LogData xml not null,
LogDate datetime not null default(getdate()),
LogBy varchar(100) not null default(system_user))
create table #Inserted (
ID int primary key,
Col1 varchar(100),
Col2 int)
create table #Deleted (
ID int primary key,
Col1 varchar(100),
Col2 int)
insert into #Inserted (ID, Col1, Col2)
select 1, 'x', 1 union all
select 2, 'y', 2
insert into #Deleted (ID, Col1, Col2)
select 2, 'z', 2 union all
select 3, 'w', 3
declare @XML xml
select @xml =
(select isnull(i.ID, d.ID) as ID,
nullif(i.Col1, d.col1) as Col1_To, nullif(i.Col2, d.Col2) as Col2_To,
nullif(d.Col1, i.col1) as Col1_From, nullif(d.Col2, i.Col2) as Col2_From,
case
when i.id is not null and d.id is not null then 'U'
when i.id is null and d.id is not null then 'D'
when i.id is not null and d.id is null then 'I'
end [Action]
from #inserted i
full outer join #deleted d
on i.id = d.id
for xml auto)
insert into #Log (LogData)
select @xml
select *
from #log
cross apply logreader_rows(logdata)
cross apply logreader_table1(result)
Note: I've never actually queried XML before. Took me about an hour, just now, to look it up in BOL, try it (took a few false starts before I got how to use the nodes bit), and build these functions. Based on that, I have to say that the potential arguments against this based on complexity of querying XML don't hold up, for me at least.
(It also probably means that there's a better way to query this. I'll have to learn that later though.)
On the question about using Columns_Updated, I'm using the NullIf test and a full outer join instead. I find that easier than trying to figure out the bit values for the columns I want to test. Also, Columns_Updated (or the Update()) function, will return a positive for a column where you set it to its current value.
create table dbo.Test_Updated (
ID int identity primary key,
Col1 varchar(100))
go
set nocount on
insert into dbo.Test_Updated (Col1)
select 'x'
go
create trigger Test_UpdatedTest on dbo.Test_Updated
after update
as
if update(col1)
raiserror('Col1 updated', 10, 1);
go
update dbo.Test_Updated
set Col1 = 'x'
where Col1 = 'x'
Try that in Management Studio (again, in a test database), and you'll see that it gives you the error about updating Col1, even though it's changing "x" to "x" (no change).
My logging trigger, using NullIf, won't log that column in that update. More efficient.
As a slight defect I haven't worked out yet, if an update does nothing but set columns to their current value, it will still insert a log entry that an update happened, it just won't include anything but the ID and action. Not sure what to do about that. Such updates shouldn't be common, though.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply