473,289 Members | 1,866 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,289 software developers and data experts.

Trigger failing after table structure change

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

Jul 20 '05 #1
6 3423
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

Jul 20 '05 #2
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
Jul 20 '05 #3
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

Jul 20 '05 #4
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
Jul 20 '05 #5
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

Jul 20 '05 #6
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
Jul 20 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Trevor Fairchild | last post by:
I am trying to create a very minimal auditing system for a series of databases. I am in the process of writing Update triggers for 5 Tablse. I will write a trigger for each table-the trigger's...
1
by: shottarum | last post by:
I currently have 2 tables as follows: CREATE TABLE . ( mhan8 int, mhac02 varchar(5), mhmot varchar(5), mhupmj int )
33
by: coosa | last post by:
I have a table: ---------------------------------------------------- CREATE TABLE CATEGORY ( CATEGORY_ID INTEGER IDENTITY(1,1) NOT NULL, CATEGORY_NAME VARCHAR(40) NOT NULL,...
10
by: Axel | last post by:
Hello, I would like to create a (what I believe is) simple trigger that updates a row in one table based on updates of corresponding fields of same row. Its a "week total" field that sums up...
6
by: JohnO | last post by:
Hi Folks, I have an update trigger that fails (it inserts an audit table record) in some circumstances. This is causing the triggering transaction to fail and roll back. Is there any way to...
2
by: mob1012 via DBMonster.com | last post by:
Hi All, I wrote last week about a trigger problem I was having. I want a trigger to produce a unique id to be used as a primary key for my table. I used the advice I received, but the trigger is...
5
by: wpellett | last post by:
I can not get the SQL compiler to rewrite my SQL UPDATE statement to include columns being SET in a Stored Procedure being called from a BEFORE UPDATE trigger. Example: create table...
2
by: dean.cochrane | last post by:
I have inherited a large application. I have a table which contains a hierarchy, like this CREATE TABLE sample_table( sample_id int NOT NULL parent_sample_id int NOT NULL ....lots of other...
6
by: Oliver | last post by:
I'm fairly new to DB2. I have been assigned to build a delete trigger that finds the data type of each of the table's fields so that the trigger can then build a string consisting of OLD values...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.