Trigger failing after table structure change

[Dave C.]

Hello,

I have created the following trigger:

CREATE TRIGGER tr_UDFTest ON UserDefinedFields FOR INSERT, UPDATE
AS
DECLARE @foobar varchar(100)

SELECT @foobar= foobar FROM inserted
IF ( @foobar = 'foobar') INSERT INTO LogTable (LogText) values ('Found foobar')
ELSE INSERT INTO LogTable (LogText) values ('Did not find foobar')
GO
Basically, the trigger monitors the values begin put in the foobar field,
and acts accordingly based on what it finds. In practice, my needs are a
bit more complex (the trigger will be programmatically generated, based on
a set of rules) but the principle is much the same.

ErrorTable is defined as :

create table LogTable (LogText varchar(128))

UserDefinedFields is a table whose definition may change depending on the
user's needs, but for now assume it contains a varchar column called
foobar.

My problem is that if the user's needs change, and they remove the field
foobar, the trigger causes all subsequent inserts/updates to fail with an
error indicating the column foobar doesn't exist. (Which makes sense of
course!)

CREATE TRIGGER tr_UDFTest ON UserDefinedFields FOR INSERT, UPDATE
AS
DECLARE @foobar varchar(100)

if not exists (select * from syscolumns sc inner join sysobjects so on sc.id = so.id
where sc.name = 'foobar'
and so.name = 'UserDefinedFields') BEGIN
INSERT INTO LogTable (LogText) values ('Error : Foobar column does not exist!')
RETURN
END

SELECT @foobar= foobar FROM inserted
IF ( @foobar = 'foobar') INSERT INTO LogTable (LogText) values ('Found foobar')
ELSE INSERT INTO LogTable (LogText) values ('Did not find foobar')
GO
I'd be happy with the above 'flavor' of solution (bailing, or logging an
error and bailing, when we hit unexpected problems) as long as the
inserts/updates don't fail otherwise. Perhaps I can nest a transaction, or
supress a RAISEERROR or something?

The cleanest solution would probably be to change a bunch of client
software such that it won't remove the foobar field if this field is
needed for a trigger (foreign key constraints based on the set of rules
I'm using are a nice and intuitive solution). Unfortunately, that doesn't
work well with my timeframe (done by thursday) as changing the client
software is impossible by then. Any ideas or suggestions? Platform is
Win2k, SQL 2000 Enterprise (I think enterprise, certainly 2000).

thanks,
Dave

[Dave C.]

On Fri, 17 Oct 2003, Dave C. wrote:

My problem is that if the user's needs change, and they remove the field
foobar, the trigger causes all subsequent inserts/updates to fail with an
error indicating the column foobar doesn't exist. (Which makes sense of
course!)

CREATE TRIGGER tr_UDFTest ON UserDefinedFields FOR INSERT, UPDATE
AS
DECLARE @foobar varchar(100)

if not exists (select * from syscolumns sc inner join sysobjects so on sc.id = so.id
where sc.name = 'foobar'
and so.name = 'UserDefinedFields') BEGIN
INSERT INTO LogTable (LogText) values ('Error : Foobar column does not exist!')
RETURN
END

SELECT @foobar= foobar FROM inserted
IF ( @foobar = 'foobar') INSERT INTO LogTable (LogText) values ('Found foobar')
ELSE INSERT INTO LogTable (LogText) values ('Did not find foobar')
GO

Oops, deleted a sentence. In case is wasn't clear, the second trigger
definition (quoted above) was a workaround that I tried, but which
was not successful, but whose 'flavor' (logging an error and otherwise
continuing with the insert/update) I'd be happy with.

Dave

[Erland Sommarskog]

Dave C. (me***@rules.spam) writes:

CREATE TRIGGER tr_UDFTest ON UserDefinedFields FOR INSERT, UPDATE
AS
DECLARE @foobar varchar(100)

SELECT @foobar= foobar FROM inserted
IF ( @foobar = 'foobar') INSERT INTO LogTable (LogText) values
('Found foobar')
ELSE INSERT INTO LogTable (LogText) values ('Did not find foobar')
GO
There is a problem with this trigger. If more than one row is inserted
or updated in the same statement, you are only logging one of these
rows, since a trigger fires once per statement.
My problem is that if the user's needs change, and they remove the field
foobar, the trigger causes all subsequent inserts/updates to fail with an
error indicating the column foobar doesn't exist. (Which makes sense of
course!)
And the entire transaction is rolled back, so there is no risk that
any data is updated without be audited.
The cleanest solution would probably be to change a bunch of client
software such that it won't remove the foobar field if this field is
needed for a trigger (foreign key constraints based on the set of rules
I'm using are a nice and intuitive solution). Unfortunately, that doesn't
work well with my timeframe (done by thursday) as changing the client
software is impossible by then. Any ideas or suggestions? Platform is
Win2k, SQL 2000 Enterprise (I think enterprise, certainly 2000).

If I understand this you have some tables that the user can change on
the fly. That sounds like a bad design to me. When you change a table,
for instance drop a column, any codes that refer to that column needs
to be revised. Seems like you should consider regenerate that trigger
in some automatic fashion.

You should also investigate third party tools. The best tools for auditing
are probably SQL Audit (www.redmatrix.com) and Entegra (www.lumigent.com).
SQL Audit works with triggers, whereas Entegra works from the transaction
log.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Dave C.]

On Sun, 19 Oct 2003, Erland Sommarskog wrote:

There is a problem with this trigger. If more than one row is inserted
or updated in the same statement, you are only logging one of these
rows, since a trigger fires once per statement.
Yes, I know. I was trying to get the simple case of a single row
update/insert working first, then it would be a relatively simple step to
extend it to the multiple case. Thanks, though.

My problem is that if the user's needs change, and they remove the field
foobar, the trigger causes all subsequent inserts/updates to fail with an
error indicating the column foobar doesn't exist. (Which makes sense of
course!)

And the entire transaction is rolled back, so there is no risk that
any data is updated without be audited.

In this case, rolling back the transaction would cause undesirable
behavior in an end user GUI app, which I want to avoid. This entire issue
is part of a short term customization for a single customer, and it's more
important for me to keep the standard software working as expected than
audit the data (the decision being made here is of relatively minor
importance).

If I understand this you have some tables that the user can change on
the fly. That sounds like a bad design to me. When you change a table,
for instance drop a column, any codes that refer to that column needs
to be revised. Seems like you should consider regenerate that trigger
in some automatic fashion.
You are absolutely right on all counts. Problems are that I didn't write
the original software, just this customization, and that this
customization needs to be in place by thursday. Regenerating the trigger
at the right time would involve changes to the standard software release,
rather than just this cusomtization, and there's not way I could put that
change into effect by then (it's not under my control!). If this isn't
done by thursday, we don't get paid, etc etc etc. As much as I agree with
you ideally, I can't implement the correct solution as you suggest. So
basically I was looking for a way to not have the trigger completely roll
back the transaction, and runtime checking of the existence of the column,
like the following, wasn't working as I had hoped:
if not exists (select * from syscolumns sc inner join sysobjects so on sc.id = so.id
where sc.name = 'foobar'
and so.name = 'UserDefinedFields') BEGIN
INSERT INTO LogTable (LogText) values ('Error : Foobar column does not exist!')
RETURN
END

I suspect it was because the trigger is compiled ahead of time, but I am
not sure.

Dave

[Erland Sommarskog]

Dave C. (me***@rules.spam) writes:

In this case, rolling back the transaction would cause undesirable
behavior in an end user GUI app, which I want to avoid. This entire issue
is part of a short term customization for a single customer, and it's more
important for me to keep the standard software working as expected than
audit the data (the decision being made here is of relatively minor
importance).

One alternative would be set up replication and do the auditing in the
data in the replicated database. That is the triggers would be over
there and not in the source database. If you try to an audit a column
that does not exist, I would guess holds up replication, but you are
not disrupting the GUI. Since you would have to stop and restart
replication, you would lose auditing.

Now, replication is nothing to take lightly, and you may not get it
working smoothly by Thursday. And there will be issue for future
changes to the database.

The only other alternative is to use dynamic SQL, but this too requires
carefulness:

IF col_length('tbl', 'col') IS NOT NULL
BEGIN
INSERT LogTable (LogText)
EXEC ('IF EXISTS (SELECT * FROM #tbl_inserted
WHERE fobar = ''fobar'')
SELECT ''Found "fobar"''
ELSE
SELECT ''Did not find "fobar"''')
END

#tbl_inserted is here created in the beginning of the trigger with

SELECT * INTO #tbl_inserted FROM inserted

Make sure that the name of the temp table is unique for each trigger,
in case triggers cascade.

col_length() is a trick to avoid direct refernces to the system tables.

If you have never used dynamic SQL before, there are a couple of things
you need to know before you start hacking. I have an article on my web
site on this topic: http://www.algonet.se/~sommar/dynamic_sql.html.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Dave C.]

On Mon, 20 Oct 2003, Erland Sommarskog wrote:

One alternative would be set up replication and do the auditing in the
data in the replicated database. That is the triggers would be over
there and not in the source database. If you try to an audit a column
that does not exist, I would guess holds up replication, but you are
not disrupting the GUI. Since you would have to stop and restart
replication, you would lose auditing.

Now, replication is nothing to take lightly, and you may not get it
working smoothly by Thursday. And there will be issue for future
changes to the database.
I can see how that would work. I've actually done a great deal of work
wit replication, and in this case I feel it would make the problem *much*
more complex than it needs to be.

The only other alternative is to use dynamic SQL, but this too requires
carefulness:
I like this idea, actually.

IF col_length('tbl', 'col') IS NOT NULL
BEGIN
INSERT LogTable (LogText)
EXEC ('IF EXISTS (SELECT * FROM #tbl_inserted
WHERE fobar = ''fobar'')
SELECT ''Found "fobar"''
ELSE
SELECT ''Did not find "fobar"''')
END

#tbl_inserted is here created in the beginning of the trigger with

SELECT * INTO #tbl_inserted FROM inserted

Make sure that the name of the temp table is unique for each trigger,
in case triggers cascade.
Wow! I really should have thought of trying 'Select Into' on my own. I
believe this is exactly what I was looking for, since the dynamic SQL
should allow me to analyze table structures at run time and act
accordingly. Thanks a great deal for your help.

col_length() is a trick to avoid direct refernces to the system tables.
Thanks again, this is a useful tip I'm sure I'll use again.
If you have never used dynamic SQL before, there are a couple of things
you need to know before you start hacking. I have an article on my web
site on this topic: http://www.algonet.se/~sommar/dynamic_sql.html.

The article is is good one. Luckily for me, I do not expect performance or
any of the other considerations you list to be a problem in this
particular case.

Dave

[Erland Sommarskog]

Dave C. (me***@rules.spam) writes:

The article is is good one. Luckily for me, I do not expect performance or
any of the other considerations you list to be a problem in this
particular case.

Be very careful with complex code in a trigger, because in a trigger you
are always in the context of a transaction, which means that you are holding
locks. Of course, if this is an application where the users only
occasionally makes some updates, this is not much of an issue. But in a
busy system with data arriving in real-time, you could get contention
issues.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp