[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

debug a trigger, very quickly

Posted on 2009-02-12
20
Medium Priority
?
385 Views
Last Modified: 2012-06-21
sql v2000, My objective is to audit all updates or deletions to varying tables.  
I created the trigger posted below after researching this:
http://www.nigelrivett.net/SQLTriggers/AuditTrailTrigger.html

I tested it out several times, on each of the three relevant tables, all possible updates and deletions -- it worked perfectly.  All updates and deletions were recorded within my audit trail table.

So, I push it out to production.  Immediately thereafter I am told there is a problem at the front end.  See, the endusers that view/edit the tables (that will fire the triggers), are doing so via a web interface.  Basically, they attempt to edit and receive this:

Warning: mssql_query() [function.mssql-query]: message: Incorrect syntax near the keyword 'and'. (severity 15) in C:\Inetpub\..........\tableeditor_save.php on line 11

Warning: mssql_query() [function.mssql-query]: Query failed in C:\Inetpub\..........tableeditor_save.php on line 11

Again, every execution from within QA works just fine.  It's just through the interface that we are seeing this error:   'Incorrect syntax near the keyword 'and'. '

I figured I'd try to debug it, PRINT instead of EXEC, but I'm not finding anything.  Are there any suggestions as to what this could be, or how to effectively debug this trigger?

create trigger [triggername] on [dbo].[tableName] for update, delete
as
declare @bit int,
@field int ,
@maxfield int ,
@char int ,
@fieldname varchar(128) ,
@TableName varchar(128) ,
@PKCols varchar(1000) ,
@sql varchar(5000), 
@UpdateDate varchar(21) ,
@UserName varchar(128) ,
@Type char(1) ,
@PKSelect varchar(1000)
	
select @TableName = 'TableName'
-- date and user
select 	@UserName = system_user ,@UpdateDate = convert(varchar(8), getdate(), 112) + ' ' + convert(varchar(12), getdate(), 114)
 
-- Action
if exists (select * from inserted)
if exists (select * from deleted)
	select @Type = 'U'
	else
		select @Type = 'I'
	else
		select @Type = 'D'
	
-- get list of columns
select * into #ins from inserted
select * into #del from deleted
 
-- Get primary key columns for full outer join
select	@PKCols = coalesce(@PKCols + ' and', ' on') + ' i.' + c.COLUMN_NAME + ' = d.' + c.COLUMN_NAME
from	INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where 	pk.TABLE_NAME = @TableName
and	CONSTRAINT_TYPE = 'PRIMARY KEY'
and	c.TABLE_NAME = pk.TABLE_NAME
and	c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
	
-- Get primary key select for insert
select @PKSelect = coalesce(@PKSelect+'+','') + '''<' + COLUMN_NAME + '=''+convert(varchar(100),coalesce(i.' + COLUMN_NAME +',d.' + COLUMN_NAME + '))+''>''' 
from	INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where 	pk.TABLE_NAME = @TableName
and	CONSTRAINT_TYPE = 'PRIMARY KEY'
and	c.TABLE_NAME = pk.TABLE_NAME
and	c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
	
if @PKCols is null
begin
	raiserror('no PK on table %s', 16, -1, @TableName)
	return
end
	
select @field = 0, @maxfield = max(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName
while @field < @maxfield
begin
	select @field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION > @field
	select @bit = (@field - 1 )% 8 + 1
	select @bit = power(2,@bit - 1)
	select @char = ((@field - 1) / 8) + 1
	if substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0 or @Type in ('I','D')
	begin
		select @fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION = @field
		select @sql = 		'insert AuditTrail (Type, TableName, PK, FieldName, OldValue, NewValue, UpdateDate, UserName)'
		select @sql = @sql + 	' select ''' + @Type + ''''
		select @sql = @sql + 	',''' + @TableName + ''''
		select @sql = @sql + 	',' + @PKSelect
		select @sql = @sql + 	',''' + @fieldname + ''''
		select @sql = @sql + 	',convert(varchar(1000),d.' + @fieldname + ')'
		select @sql = @sql + 	',convert(varchar(1000),i.' + @fieldname + ')'
		select @sql = @sql + 	',''' + @UpdateDate + ''''
		select @sql = @sql + 	',''' + @UserName + ''''
		select @sql = @sql + 	' from #ins i full outer join #del d'
		select @sql = @sql + 	@PKCols
		select @sql = @sql + 	' where i.' + @fieldname + ' <> d.' + @fieldname 
		select @sql = @sql + 	' or (i.' + @fieldname + ' is null and  d.' + @fieldname + ' is not null)' 
		select @sql = @sql + 	' or (i.' + @fieldname + ' is not null and  d.' + @fieldname + ' is null)' 
	exec (@sql)
    end
end

Open in new window

0
Comment
Question by:dbaSQL
  • 13
  • 7
20 Comments
 
LVL 17

Author Comment

by:dbaSQL
ID: 23622782
Any ideas?
0
 
LVL 41

Expert Comment

by:ralmada
ID: 23630155
Make sure no variable is NULL when building the @sql string.
0
 
LVL 41

Expert Comment

by:ralmada
ID: 23630182
Also on line 74 is not quite right... There is a space missing and something is not quite right in the way you are using the full outer join. the syntax should be like
full outer join yourtable d on d.fieldname = c.fieldname
 Try like this

select @sql = @sql + 	' from #ins i full outer join #del d on '
select @sql = @sql + 	@PKCols --I guess @PKCols is a string containing something like 'd.yourfieldname = i.yourfieldname' If not you will have to rewrite this

Open in new window

0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 17

Author Comment

by:dbaSQL
ID: 23631865
hi ralmada.  i don't know about line 74 being incorrect -- remember, all of my executions in query analyzer work just fine.  It's only from the web interface that we are failing.  I attempted your suggestion, and it fails w/this:

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'on'.

>>Make sure no variable is NULL when building the @sql string.
I wonder if this may be more to the point.  I'm no PHP gal, but I am told that PHP will display NULLS as blanks.  I have attached the output from exactly what PHP is doing to my procedure.  The only required parm is @hostid - they can update one or all of the other attributes for the given @hostid.  

Possibly all of the blanks are what he's not passing in at the weblayer.  What do you think?

exec procname @hostname='XXXXXX', @host_use='Support', @users='XXXXX',
@desk='Operations', @status='Production', @eos='', @os='Windows', @version='XP Professional',@make='HP', @model='xw6000',
@generation='', @eth0_ipaddr='', @ilo_ipaddr='', @cpu_number=2, @cpu_model='Pentium 4 (HT)', @cpu_freq='2.8GHz', @ram='2048MB', @isin_nagios='0', @disk_monitor='0', @requestor='', @team='I', @type='Workstation', @shipping_label='',@colo='ch', @serial='ABC', @eol='', @production_date='', @sched_decom='',@upgrade_qtr='', @notes='testing 1 2 3', @reboot='', @service_pack='sp 2', @minor_version=0,@is_idle='0', @is_virtual='0', @switch_ip_port='', @eth0_mac='', @eth1_ipaddr='', @eth1_mac='',@eth2_ipaddr='', @eth2_mac='', @eth3_ipaddr='', @eth3_mac='', @eth4_ipaddr='', @eth4_mac='',@ilo_mac='', @cage='', @rack_id='', @rack_u='', @is_donation='0', @purchase_date='08/13/09',@asset_amt=2799.74, @last_mod='Aug 19 2008 1:06PM', @hostid=771

Open in new window

0
 
LVL 41

Accepted Solution

by:
ralmada earned 1500 total points
ID: 23633585
I don't think the problem is with your stored procedure. But, with the NULL values in the variables used in the trigger. So when creating the @sql string if any variable is null, your string will be truncated, hence, when you run exec (@sql), your will get a syntax error.
For instance to define your joining condition you are doing this:
select @PKCols = coalesce(@PKCols + ' and', ' on') + ' i.' + c.COLUMN_NAME + ' = d.' + c.COLUMN_NAME
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = @TableName
and CONSTRAINT_TYPE = 'PRIMARY KEY'
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
 
I don't have a SQL server to test here, but can you run this and check what you get?
declare @PKCols varchar(100)
select @PKCol+' and'
If you get NULL then you are fine, because then with the coalesce function the result will be ' on' otherwise you will have a problem when creating your join because you will end up with something like this "....  from #ins i full outer join #del d and ...' which is wrong, instead of  the correct way which is " from #ins i full outer join #del d on ...'
Also what if you don't have any primary key ? then you will end up with a @PKCols variable like this: " on i. = d. ", without names... So when you put that in your @sql string, you will have a syntax error. The same concept should apply to all the fields. You have to contemplate all the possibilities to avoid ending up with flawed @sql string. probably adding more conditions when building the string.
Hope that was clear enough.
ralmada
0
 
LVL 41

Expert Comment

by:ralmada
ID: 23633604
I meant

declare @PKCols varchar(100)
print @PKCol+' and'
0
 
LVL 41

Expert Comment

by:ralmada
ID: 23633797
Also, in line 49 when you define your @fieldname variable
select @fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION = @field
What if the column name has a space in between? i.e. "First Name". You should consider using brackets to be safe...
 select @fieldname = "["+COLUMN_NAME+"]" from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION = @field
and in this case ensure that COLUMN NAME exists otherwise you could endup with something like this @fieldname = "[]"
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 23633863
>.What if the column name has a space in between?
Some have underscores, like this:  'shipping_lable', but no spaces.  I won't allow for that.

I'm working to test your suggestion now.  back shortly
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 23633987
declare @pkcols varchar(100), @tablename varchar(35)
set @tablename = 'tablename'

select      @PKCols = coalesce(@PKCols + ' and', ' on') + ' i.' + c.COLUMN_NAME + ' = d.' + c.COLUMN_NAME
from      INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where       pk.TABLE_NAME = @TableName
and      CONSTRAINT_TYPE = 'PRIMARY KEY'
and      c.TABLE_NAME = pk.TABLE_NAME
and      c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

print @pkcols+' and'


I get this:      ' on i.hostid = d.hostid and'


not sure if it factors in, but there is one character (a space) before the 'on' and no characters after the 'and'
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 23633993
maybe that's it
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 23634091
well, i had hoped it found it... but no.  any ideas?
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 23634410
remember, the error is        Incorrect syntax near the keyword 'and'.
i have the trigger printing instead of exec'ing, and I ran this:
exec procname @hostid = 382,@ram='2048MB',@notes='testing'

trigger outputs this:
(i gave all values a line of it's own - other than that, no changes to the output)

insert dbo.Audit
(Type, TableName, PK, FieldName, OldValue, NewValue, UpdateDate, UserName)
select 'U',
'tablename',
'<hostid='+convert(varchar(100),coalesce(i.hostid,d.hostid))+'>',
'notes',
convert(varchar(1000),d.notes),
convert(varchar(1000),i.notes),
'20090213 10:14:55:683',
'myworkstation\myname'
from #ins i full outer join #del d
on  i.hostid = d.hostid
where i.notes <> d.notes
or (i.notes is null and  d.notes is not null)
or (i.notes is not null and  d.notes is null)

i also asked the web guy to capture the output there.  interesting, i only attempted to update @ram and @notes, and he pushes all that crap, but this is what PHP does to it:

exec procname @hostname='10002', @host_use='Spare', @users='IT', @desk='London Office', @status='Spare', @eos='', @os='Windows', @version='XP Professional', @make='HP', @model='xw6000', @generation='', @eth0_ipaddr='', @ilo_ipaddr='', @cpu_number=2, @cpu_model='Pentium 4 (HT)', @cpu_freq='2.8 GHz', @ram='4048', @isin_nagios='0',
@disk_monitor='0', @requestor='username', @team='I', @type='Workstation', @shipping_label='', @colo='ch', @serial='xyz', @eol='', @production_date='2007-04-30', @sched_decom='', @upgrade_qtr='', @notes='', @reboot='', @service_pack='sp 2', @minor_version=0, @is_idle='0', @is_virtual='0', @switch_ip_port='', @eth0_mac='', @eth1_ipaddr='', @eth1_mac='', @eth2_ipaddr='', @eth2_mac='', @eth3_ipaddr='', @eth3_mac='', @eth4_ipaddr='null', @eth4_mac='null', @ilo_mac='', @cage='', @rack_id='', @rack_u='', @is_donation='0', @purchase_date='', @asset_amt=0.00, @last_mod='Dec 9 2008 2:51PM', @hostid=382


at this point, I am disgusted with the PHP and i just need an updated and a delete trigger that will write the necessary values to my audit table if/when the other tables are altered.  i will keep trying
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 23634496
clarification, when i print the @sql, the trigger actually outputs 20 lines like what i posted up there for the insert into Audit.  I believe this is because the table in question has 20 attributes.  Because I was firing the update on @ram and @notes, I only looked at those two of the outputted lines.  i pasted the one for @ram here, so you have them both.  but, remember, they don't fail in QA.  only when fired at the web layer.  at this point, very hard to say if it is the trigger or the update proc, but PHP is shutting me down somewhere.... miserably.  

insert dbo.Audit
(Type, TableName, PK, FieldName, OldValue, NewValue, UpdateDate, UserName)
select 'U',
'Hosts',
'<hostid='+convert(varchar(100),coalesce(i.hostid,d.hostid))+'>',
'ram',
convert(varchar(1000),d.ram),
convert(varchar(1000),i.ram),
'20090213 10:14:55:683',
'myworkstation\myname
from #ins i full outer join #del d
on  i.hostid = d.hostid
where i.ram <> d.ram
or (i.ram is null and  d.ram is not null)
or (i.ram is not null and  d.ram is null)
0
 
LVL 41

Expert Comment

by:ralmada
ID: 23636304
From your last posting there is problem with the query generated. The last column is missing a quote.
'myworkstation\myname' <--- Missing quote.
 
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 23636532
yes, that was just a type of of my own.  sorry.  i am soooooooo tired of this one....
i tried the one posted below, it is failing now with this:

Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

i couldn't get it to work with update AND delete, so i figured i'd just do two per table (though i'd rather only have one per table)    and now it, too, is failing

ugh

CREATE TRIGGER tr_tablenameUpdate on tablename
FOR UPDATE
AS
 
 
declare @bit int ,
   @field int ,
   @char int ,
   @fieldname varchar(128) ,
   @sql varchar(1000),
   @dataType varchar(255),
   @tblName varchar(255),
   @MaxColID int
 
select * into #del from deleted
select * into #ins from inserted
 
select @field = 0, @MaxColID = 0, @tblName = 'tablename'
select @MaxColID= max(colid) from syscolumns where id = (object_id(@tblName))
 
while @field <= (@MaxColID)
begin
     select @field = @field + 1
     select @bit = (@field - 1 )% 8 + 1
     select @bit = power(2,@bit - 1)
     select @char = ((@field - 1) / 8) + 1
 
     if substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0
     begin
          select @fieldname = name, @datatype = xtype from syscolumns where colid = @field and id = object_id(@tblName)
--          select @dataType = data_type from information_schema.columns where table_name = @tblName and column_name = @fieldname
     
          if @dataType = 56 --'int'
               select @sql = 'insert dbo.Audit (pk,type,tablename,fieldname,oldvalue,newvalue,updatedate,username) 
	       select #ins.hostid, ''U'',''' + @tblName + ''',''' + @fieldname + ''',convert(varchar(500),#del.' + @fieldname + '), 
               convert(varchar(500),#ins.' + @fieldname + '), ''' + convert(varchar(50), current_timestamp) +''','''+SYSTEM_USER+ ''' 
	       from #del inner join #ins on #del.hostid = #ins.hostid
               where isnull(#del.' + @fieldname + ', 0) <>      isnull(#ins.' + @fieldname + ', 0)'
          else
               select @sql = 'insert dbo.Audit (pk,type,tablename,fieldname,oldvalue,newvalue,updatedate,username) 
      	       select #ins.hostid, ''U'',''' + @tblName + ''',''' + @fieldname + ''',convert(varchar(500),#del.' + @fieldname + '), 
               convert(varchar(500),#ins.' + @fieldname + '), ''' + convert(varchar(50), current_timestamp) +''',''' +SYSTEM_USER+ ''' 
	       from #del inner join #ins on #del.hostid = #ins.hostid
               where isnull(#del.' + @fieldname + ', '''') <> isnull(#ins.' + @fieldname + ', '''')'
 
          EXEC (@sql) 
--PRINT (@sql)
 
     end
end

Open in new window

0
 
LVL 17

Author Comment

by:dbaSQL
ID: 23636615
In debug mode, that trigger gives me this:

insert dbo.Audit (pk,type,tablename,fieldname,oldvalue,newvalue,updatedate,username)
select #ins.hostid, 'U','tablename','notes',convert(varchar(500),#del.notes),
convert(varchar(500),#ins.notes), 'Feb 13 2009  1:49PM','myworkstation\myname'
from #del inner join #ins on #del.hostid = #ins.hostid
where isnull(#del.notes, '') <> isnull(#ins.notes, '')

it's got to be the #ins.hostid, because if i just do this, it works just fine:

insert dbo.Audit (pk,type,tablename,fieldname,oldvalue,newvalue,updatedate,username)
select 1, 'U','tablename','ram','8192MB','2048MB','Feb 13 2009  1:49PM','myworkstation\myname'

PK is varchar(1000) in the Audit table
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 23637177
it may be due to the COLUMNS_UPDATED, because my table has more than 8 columns
(per BOL, 'Create Trigger', the use of 'COLUMNS_UPDATED'

do you have any suggestions? potentially i am just overcomplicating this.  .........
0
 
LVL 17

Author Closing Comment

by:dbaSQL
ID: 31546067
I appreciate the input, ralmada.  I must get this done today, though, so I have opened another inquiry, in hopes of getting some correction on this asap.  Again, I appreciate your input.  I will award and close, and I'll get back to you on this when I've corrected it... just to let you know what I found.
0
 
LVL 41

Expert Comment

by:ralmada
ID: 23663585
thanks for the points. I wish I could have done more to help you. Maybe it will help if you post a question on the PHP zone as well. I will post a comment if I found something that could help you.
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 23663709
i did copy the PHP zone on the new inquiry.  still nothing, but i am working it.  i hope somebody sees it and has an idea
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question