April 5, 2009 at 10:56 pm
Why won't this work?
ALTER TRIGGER [trgInsertAudit]
ON [dbo].[tblEmployeeId]
for update
AS
BEGIN
declare @tmpOldValue varchar(200)
declare @tmpNewValue varchar(200)
set @tmpOldValue = 'select SSN4 from Deleted where employeeid = ' + '1'
select @tmpOldValue
exec(@tmpOldValue)
END
Calling SQL:
Update tblEmployeeID
set SSN4 = '9811'
where EmployeeID = '1'
I get:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'Deleted'.
The select statement returns the correct SQL Query:
select SSN4 from Deleted where employeeid = 1
Why is 'Deleted' an invalid object
April 5, 2009 at 11:06 pm
Apparently the Deleted pseudotable is not available in the subordinate batch that your dynamic SQL has to run in. Try dumping Deleted into a temp table first.:
ALTER TRIGGER [trgInsertAudit]
ON [dbo].[tblEmployeeId]
for update
AS
BEGIN
declare @tmpOldValue varchar(200)
declare @tmpNewValue varchar(200)
Select * Into #Del From Deleted
set @tmpOldValue = 'select SSN4 from #Del where employeeid = ' + '1'
select @tmpOldValue
exec(@tmpOldValue)
END
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 6, 2009 at 9:04 am
Thank you. That helped. Now to put it back in context, how can I accomplish assigning the scalar value returned from the select statement to a variable:
set @mySQLString = 'select SSN4 from #Del where employeeid = ' + '1'
? @myvariablename = exec mySQLString ?
April 6, 2009 at 9:13 am
Look up sp_executesql. It allows passing of parameters to and from dynamic SQL.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 6, 2009 at 10:33 am
Simple terms you can't magic tables in any kind of dynamic queries.
April 6, 2009 at 11:36 am
This is what I come up with following examples on MSDN:
declare @tmpOldQuery nvarchar(200)
declare @ParamDef nvarchar(200)
declare @rtnVal nvarchar(200)
SELECT @tmpOldQuery = N'select @rtnVal = select @fieldname1 from #Del where employeeid = 1'
Select @ParamDef = N'@fieldname1 nvarchar(200), @rtnVal nvarchar(200) OUTPUT'
@fldname = 'SSN4'
EXEC dbo.sp_executesql tmpOldQuery,
@ParamDef,
@fieldname1=@fieldname,
@rtnVal=@rtnvalx output
The rtnValx part doesn't make sense to me either but it seems that what the samples are recommending to pull an
extra value out of the air.
I get "Incorrect syntax near 'tmpOldQuery'. Before I get the 'What were you thinking?" replies, i chose fieldname1 because there was already a @fieldname variable declared.
The following also produced errors:
EXEC dbo.sp_executesql tmpOldQuery,
@ParamDef,
@fieldname1=@fieldname,
@rtnVal output
The error was:
sp_executesql output Must pass parameter number 4 and subsequent parameters as '@name = value
So I'm saying this is confusing. The MSDN examples are not the easiest things to read.
April 6, 2009 at 12:23 pm
I think that 'tmpOldQuery' should be '@tmpOldQuery'.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 6, 2009 at 12:31 pm
I realized that error when I got from lunch. Corrected I still got the error:
Incorrect syntax near the keyword 'select'.
April 6, 2009 at 1:51 pm
Hi
One "SELECT" too much... 😉
SELECT @tmpOldQuery = N'select @rtnVal = @fieldname1 from #Del where employeeid = 1'
Select @ParamDef = N'@fieldname1 nvarchar(200), @rtnVal nvarchar(200) OUTPUT'
@fldname = 'SSN4'
EXEC dbo.sp_executesql tmpOldQuery,
@ParamDef,
@fieldname1=@fieldname,
@rtnVal=@rtnvalx output
Greets
Flo
April 6, 2009 at 6:25 pm
I am a vb.net programmer havee never had to use dynamic SQL or Triggers or cursors (and never been at a company which used them). So I am proud of my auditing technique to determine those fields which were actually updated for an UPDATE trigger. My main task:
Use a cursor to walk each corresponding fieldname = in inserted and deleted
ex. where each inserted. (=@oldvalue). My trigger now works from your comments = no error messages however I am getting all nulls in my return values.
Here is the entire SQL Block:
-- Some declaration have been removed for simplicity
ALTER TRIGGER [trgInsertAudit]
ON [dbo].[tblEmployeeId]
for update
AS
BEGIN
declare @employeeid nchar (10)
declare @fieldname nvarchar(100)
declare @oldvalue nvarchar(50)
declare @newvalue nvarchar(50)
declare @tmpOldQuery nvarchar(200)
declare @tmpNewQuery nvarchar(200)
declare @ParamDef nvarchar(2000)
declare @rtnVal nvarchar(1000)
set @EmployeeId =(select employeeid from inserted where employeeid = '1')
set @tablename = 'tblEmployeeId'
--- Store inserted in and deleted in temp tables to avoid 'object not found' --- error in dynamic SQL
Select * Into #Ins From Inserted
Select * Into #Del From Deleted
---- Setup values for dynamic SQL
DECLARE fieldnames_cursor CURSOR
FOR
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.Columns where TABLE_NAME = 'tblEmployeeId'
OPEN fieldnames_cursor
FETCH NEXT FROM fieldnames_cursor INTO @fieldname
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
IF @fieldname <> 'DateCreated'
BEGIN
SET @fieldname = RTRIM(@fieldname)
--- @tmpOldQuery is the SQL string to get
--- the value before UPDATE
SELECT @tmpOldQuery = N'select @rtnVal = @fieldname from #Del'
SELECT @ParamDef = N'@fieldname nvarchar(200), @rtnVal nvarchar(200) OUTPUT'
EXEC dbo.sp_executesql @tmpOldQuery, @ParamDef, @fieldname=@fieldname, @rtnVal=@rtnval output
select @oldValue as RtnVal
---
--- Similar code will be execute here to get @newvalue
---
--- Now execute stored proc that stores these values in audit table. All fields except for
--- @fieldname, @oldvalue and @newValue have been defined before the FETCH
exec [dbo].[spInsertAuditRec] @tableid, @employeeid, @tablename, @fieldname, @oldvalue, @newvalue, @changedate, @userid, @username, @action
--- FYI: The stored proc works as it should
END
END
FETCH NEXT FROM fieldnames_cursor INTO @fieldname
END
CLOSE fieldnames_cursor
DEALLOCATE fieldnames_cursor
END
April 7, 2009 at 12:40 am
Wow! That's a whole bunch of RBAR. It may work nicely for serveral updates, but if you ever do a large batch update, it's going to take comparatively a lot of time. Heh... please don't say it won't ever suffer a large batch update because it'll eventually happen. Someday someone somewhere will want to run a "cleanup" script on the table and then, BOOM!
It would be of some great help if you were to post the spInsertAuditRec stored procedure and the CREATE TABLE for the Audit table. Thanks.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 7, 2009 at 5:57 am
Hmmm! I never thought about that. Well right now I have to get it working and I'll have to remember to turn off trigger if batch update. Currently, the update is only called if a user updates information on web page. I'll fine tune later. This stored procedure works as it should, but here goes: (Remember some fields had been removed from trigger for simplicity sake)
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- drop procedure spInsertAuditRec
ALTER PROCEDURE [dbo].[spInsertAuditRec]
(
@TableID nchar(10),
@EmployeeId nvarchar (100),
@TableName nvarchar (40),
@FieldName nvarchar (40),
@OldValue nvarchar (50),
@NewValue nvarchar (50),
@ChangeDate nvarchar (50),
@userid nvarchar(50),
@username nvarchar(50) ,
@action nvarchar (20)
)
AS
INSERT INTO tblAudit
(
TableID,
EmployeeId,
TableName,
FieldName,
OldValue,
NewValue,
ChangeDate,
userid,
username,
action
)
VALUES
(
@TableID,
@EmployeeId,
@TableName,
@FieldName,
@OldValue,
@NewValue,
@ChangeDate,
@username,
@action
)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
April 7, 2009 at 7:21 am
If you disable it for batches, it invalidates the entire purpose of auditing, but then the trigger's only going to work for one row anyway. Your cursor is over the columns and the following statement assumes there is only one row in those #tables based off the inserted and deleted.
SELECT @tmpOldQuery = N'select @rtnVal = @fieldname from #Del'
So, if you ever update more than one row, only one of those rows will be audited
As an entertaining aside, if there's more than one row in the deleted and inserted tables, you have absolutely no guarantee that the values fetched for the field from the deleted table will come from the same row as the one retrieved from the inserted table.
I can probably write you something that works properly. Can you post the definition on the Audit table and of the tblEmployee table as well as the complete audit stored proc?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 7, 2009 at 7:29 am
You're a sweetie. I did have a condition on the upd and del tables but removed to strip down code.
CREATE TABLE [dbo].[tblEmployeeId](
[EmployeeId] [nvarchar](100) NOT NULL,
[SSN4] [nchar](10) NOT NULL,
[SSN5] [nchar](10) NOT NULL,
[AcctngKey] [char](10) NULL,
[Status] [nvarchar](30) NULL,
[DateCreated] [varchar](50) NULL,
[CreatedBy] [nvarchar](35) NULL,
[tableid] [nchar](10)
CONSTRAINT [DF_tblEmployeeId_tableid] DEFAULT ((1))
) ON [PRIMARY]
April 7, 2009 at 7:35 am
And the audit table please. And the full trigger, since you said you stripped stuff out of it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 37 total)
You must be logged in to reply to this topic. Login to reply