Post reply

tablename in trigger

  • July 4, 2010 at 6:51 am

    #224538

    Imagine you want to create a general trigger, which can be linked on multiple tables, e.g. a trigger developped in some .net language.

    Would there be a way to dynamically found out at runtime on which table you are linked?

    thanks,

    Jan

  • July 4, 2010 at 8:40 am

    #1188614

    I would strongly recommend that you don't create a general trigger. You'll end up doing a lot of work to compute the column names, you may or may not be able to access the inserted and deleted tables (they're only available in the trigger, not in anything the trigger calls, not any dynamic SQL. don't know about CLR)

    What I generally recommend is a stored procedure that can generate triggers for each table according to a specific requirement.

    As for the table name, see if @@ProcID returns the trigger's ID. It should. Then you can query sys.objects and get the parent for the trigger object. Ugly though.

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 4, 2010 at 4:17 pm

    #1188640

    I would second Gail's advice

    Here's an idea, be sure you read the discussion for the article as well: http://www.sqlservercentral.com/articles/Stored+Procedures/67055/

  • July 5, 2010 at 1:40 am

    #1188678

    Hello,

    That is what Gail suggested I guess

    DECLARE @parent_id int

    SET @parent_id = (

    SELECT TOP 1 parent_id FROM sys.triggers WHERE object_id=@@PROCID

    )

    SELECT OBJECT_NAME(@parent_id)

    Eralper
    SQL Server and T-SQL Tutorials and Articles
    Microsoft Certification and Certification Exams

  • July 5, 2010 at 3:04 am

    #1188696

    Or just

    SELECT OBJECT_NAME(parent_id) FROM sys.triggers WHERE object_id=@@PROCID

    No need for a TOP 1, the object_id is unique for first-class objects (anything that appears in sys.objects)

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 5, 2010 at 3:13 am

    #1188700

    Yes sure, you are right.

    Thanks

    Eralper
    SQL Server and T-SQL Tutorials and Articles
    Microsoft Certification and Certification Exams

  • July 5, 2010 at 3:40 am

    #1188710

    Thanks a lot, all of you!

    I know this isn't best practice. But I inherited a "version enabled " application where each record on each table can have a version that is or is not authorized. For this reason there is a version id that becomes part of every pk, but not of the fk's. So integrity is not even enforced, you can imagine the mess. Moreover only very few records should be in that non-authorized situation. But every query is full of manipulation code to get or don't get the latest version that is or isn't authorized for a specific user and is or isn't deleted. A mess. An imagine the performance when you have all these totaly unselective criteria as sArg's.

    I want to refactor it to a normal relational model. I need to keep backwards compatibility so I want to log changes to a seperate log in order to be authorized. I would like to use an instead of view for this and an open schema for the log. Since it is on so many tables I consider this general type of trigger and I"m fully aware of the meta-data that I need to tagle in this case.

    You will understand my trouble, this thing needs to be cleaned up. Someone has been too creative and has thrown everything relational databases is about overboard.

  • July 5, 2010 at 4:37 am

    #1188724

    Create a stored procedure that will generate the trigger code for each table, specific to that table and columns and doing what you want to do

    It's still a 'general' trigger, as each table has one that does the same thing, but the trigger won't need dynamic sQL and problems with accessing inserted and deleted.

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 5, 2010 at 4:43 am

    #1188727

    I had once created a data log system which creates table triggers dynamically.

    I have noted down my notes at SQL Server Log Tool for Capturing Data Changes by building Change Data Capture Structure for MS SQL Server 2005

    There you can check the create trigger scripts.

    The aim of this log data changes system is to save changed data into history like tables with the help of dynamically created triggers.

    I hope you can find something interesting.

    Eralper
    SQL Server and T-SQL Tutorials and Articles
    Microsoft Certification and Certification Exams

  • July 5, 2010 at 5:36 am

    #1188740

    Thanks a lot, Eralper!

  • July 5, 2010 at 9:25 am

    #1188816

    hey have you looked into 2008's change tracking? its table level and attempts to solve the very problem you have

    http://www.databasejournal.com/features/mssql/article.php/3875186/A-Look-at-SQL-Server-2008-Change-Tracking.htm

    I came across the feature a while back and filed it away under "interesting" and read your post and figured this may help...

    E.Newton

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply