Returning current table name to a trigger
-
Hi,
I am trying to make a trigger universal so i can apply it to all tables in a database. To accomplish this I would need to be able to return the current table name to the code within the trigger.
Has anyone else managed this? I can't find any system constants or functions that accomplish this without knowing the table ID / Name beforehand (and nobody else here has come across anything). I just want the tablename that the trigger is being run against. as it executes.
Steve
We need men who can dream of things that never were.
-
I'd be careful with the wisdom of writing a universal stored procedure, but one way to get your current database is:
SELECT db_name(dbid) FROM master..sysprocesses WHERE spid=@@SPID -
All things 'universal' makes me think 'dynamic SQL'.... Before you even think about walking down dynamic boulevard, read this - http://www.sommarskog.se/dynamic_sql.html - and take a good time thinking if this is a place you want to be...
Reasons like 'I don't want to type so much' are not valid reasons resorting to dynamic SQL...
..just my .02 of course
/Kenneth
-
Hard to see how you'd do this purely within T-SQL. Remember the syntax for a trigger starts
CREATE TRIGGER name
ON table
so in effect the table is part of the trigger definition. If you were to go with dynamic SQL, building the trigger text to include the table name, then you have the table name availabe in whatever context you are building the text, eg in VB
sSQL = "CREATE TRIGGER trGenericOn" & sTable & " ON " & sTable
...
' refer to current table like this
sSQL = sSQL & "SELECT TOP 1 ID FROM " & sTable & " ORDER BY ID DESC"
and so on.
In effect, the reason there isn't any way to refer to the target table within a trigger's text is because 'whoever' is creating the trigger text must already know the target table name
-
Never hear about it , suggest you giving up it.
-
mdenner: Cheers for that, working with the SPID is ok for getting at the db, it still takes a minor miracle to get down to the last inserted / updated table name.......
Ken: Read the article, very interesting and gave me some pointers but still doesn't convince me that this isn't worth a shot. Thanks.
AKM: The most helpful piece of advice up to press, maybe the key to this isnt getting to the table name within the trigger (which means jumping through horrible hoops, if it is at all possible) - it is actually taking a sproc to create said table, passing in the table name as a variable then using this variable within the same sproc to create the triggers. As you said, you have the table names when you decide to create.
newdongkui: At some point you will realise that giving up is the easy way out. Great things have never been accomplished by men not prepared to accept a challenge......
Steve
We need men who can dream of things that never were.
-
Read the article, very interesting and gave me some pointers but still doesn't convince me that this isn't worth a shot. Thanks
Some wiser German than me once stated :
"Against stupidity the gods themselves struggle in vain"
You might want to search the Google archives on the countless posting who all wanted to do this before you. And you're _very_ likely to read the same responses there that you received here.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Eagles may soar in the clouds, but weasels never get sucked into jet engines. And Steve,
It is difficult to soar like an Eagle when you work with Turkeys
Far away is close at hand in the images of elsewhere.
Anon. -
Here is code that will give you the table name to which the currently executing trigger belongs.
Create Trigger <triggerName> on <table>
for update, insert, delete
as
Select TableName = Object_Name(Parent_Obj)
From SysObjects o
Where o.ID = @@ProcID
go
Hope this is what you were looking for.
-
Guys, Guys, Guys,
Seeing you are obviously heavily (understatement) subscribed to the 'anti-dynamic' gang. I will find it hard to convince you as to wether I am right or wrong in the direction I am currently taking.
I thought, incorrectly, that as Developers - we were here to help each other in the generation of solutions to problems. Not set our little clown icons and try to amuse the population with our blindingly amusing little witicisms. If you have nothing constructive to say - not saying anything is the preferred option.
I am quite happy to take on board others opinions, will read the posts, links, follow suggested routes and make my own decision about the way to take the project forward - Influenced of course by the Project manager, Technical manager and the Company that pays my wages.
Cheers again AKM - I am up and running now.
And cheers to Shane - who's post came in whilst I was writing this. Excellent, will give that a crack shortly.
Steve
We need men who can dream of things that never were.
-
As a colleague of Steve's I resent that statement.
-
If you are trying to generate a standard trigger for each table, we wrote a VB program that does that.
It creates a script file to create a DELETE and UPDATE/INSERT trigger for each table in the database. The standard triggers updates the UserID & time of the update/insert, and inserts a record into an audit table for each change to the record. Then we modifiy the standard trigger for anything special that a particular table needs done.
I can send you the "guts" of the VB routing that generates the trigger. It would need some heavy modification to work for anyone else.
-
Shane: Works an absolute treat.
CAGreensfielder: Much appreciated. I will PM you and give you my mail address. I do have it working now but like to see all the different approaches people have taken to the same / similar problem.
So - we have gone from: "It can't be done - give up"
To: "Against stupidity the gods themselves struggle in vain"
To: "It is difficult to soar like an Eagle when you work with Turkeys"
To: Three solutions, 2 of which are working perfectly, 1 which still needs twiddling to suit our database. All of which will keep the database truly - OOooohhh that word again - dynamic
And - if you want quotes, try the inspirational - not the negatives:
"Do not go where the path may lead, go instead where there is no path and leave a trail."
"It is not because things are difficult that we do not dare; it is because we do not dare that they are difficult."
"Keep away from people who try to belittle your ambitions. Small people always do that, but the really great make you feel that you, too, can become great."
Feels like quote of the day......Steve
We need men who can dream of things that never were.
-
I thought, incorrectly, that as Developers - we were here to help each other in the generation of solutions to problems. Not set our little clown icons and try to amuse the population with our blindingly amusing little witicisms. If you have nothing constructive to say - not saying anything is the preferred option. I find this the most offensive post since I have been on this forum
This forum is to help everyone not just you
I did not post to insult or criticize anyone. I did not post anything negative, I did not have an answer straight away and it was for the benefit of everyone and especially my friend Frank.
If you do not like amusing little witicisms then don't put one on your posts
As for the final sentence, if you have nothing polite to say in response to other peoples posts then do not post at all.
It's people like you that make me want to leave great forums like this.
Far away is close at hand in the images of elsewhere.
Anon. -
Steve,
if you ask a question here in the fora, you mostly likely receive quick and probably good advise from people who already have been there where you want to go. It is completely up to you, to head such an advise or deliberately ignore it. If you decide to run against a wall it's you who will get a bleeding nose not me. So, personally I don't care which alternative you choose. And probably you have asked yourself why far more knowledgeable people than you or me advocate against this dynamic approach when it comes to databases. If you haven't find yourself asking this question, you'll be in trouble sooner or later.
However, keep in mind that people in online communities share their knowledge for free and spent their time without getting anything else but a Thank You (mostly, but always welcome) in return. If you have a different understanding here, you might be better off asking a consultant and pay for what you get there.
In a way I wish Joe Celko (who joined here recently) would respond to this thread.
Last remark: Why do you ask a question anyway, when you don't want to hear opinion that don't suit you?
Btw: I found Dave post quite amusing and not really combative or insulting. And with an autosignature like yours, you should be prepared for something like this
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply