July 11, 2010 at 3:03 pm
The fact is that trigger is getting created but not working .
I am trying to get the data of the Primary Key Column whose data is getting updated so for that i need to get the data from inserted, i knew that, but as i am storing the name of the Primary Key Column in a variable , i need to get that variable involved in my data fetching statement it's something like this:
declare @pk_name nvarchar(50)
declare @pk_value nvarchar(50)
-- i am getting the column name (PRIMARY COLUMN NAME)
set @pk_name = (select c.name
FROM sysindexes i, sysobjects t, sysindexkeys k, syscolumns c, systypes
WHEREi.id = t.id AND
i.indid = k.indid AND i.id = k.ID And
c.id = t.id AND c.colid = k.colid AND
i.indid BETWEEN 1 And 254 AND
c.xusertype = systypes.xusertype AND
(i.status & 2048) = 2048 AND t.id = OBJECT_ID('a') )
-- not getting the data, though the trigger is getting executed, but while data is getting updated then the trigger is getting fired --with a error showing "INVALID OBJECT NAME INSERTED" that inserted is not getting recognized as a virtual table name in trigger
Set @pk_value = 'select ' + @pk_name + ' from inserted'
exec sp_executeSQL @pk_value
[font="Comic Sans MS"]Rahul:-P[/font]
July 11, 2010 at 5:38 pm
Dynamic SQL doesn't run in the same context as the trigger, so that's why it doesn't exist.
Why are you trying to do it this way? What is your business requirement?
Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
Ramblings of a DBA (My SQL Server Blog)[/url]
Subscribe to my blog
July 11, 2010 at 6:36 pm
Actually i need a table which will track the update log,
i mean when a particular table gets updated then the trigger will get fired and the table name, update time, primary column name of that table, primary column value, update column name, updated data, n previous data will get stored in the table that i have created for storing the update log
I am getting the table name , primary column name of that table and the update time only
but i am still left out
i) primary column value,
iii) update column name,
iii) updated data, n
iv) previous data
I am not getting the first one and the 2nd one, so i think that's way i am not begin able to get the 3rd n 4th values as well..
can u plzzzzzzzz help me to get this done
thanks..
[font="Comic Sans MS"]Rahul:-P[/font]
July 11, 2010 at 11:22 pm
Don't use dynamic SQL in your triggers. Within dynamic SQL you can't see the inserted or deleted tables, you can't use the updated or columns_updated functions.
Rather use dynamic SQL in a stored proc to generate the triggers for you, and avoid dynamic sql and all references to the system tables in triggers.
p.s. Welcome Tara. Nice to see you here.
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 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply