Professional Web Applications Themes

A Trigger's Parent - Microsoft SQL / MS SQL Server

Howard: If you're using ERwin, you can use %TableName within the trigger definition. If you're not, the following sample should help: create table TestTable (keycol int PRIMARY KEY, attcol int) go create trigger iud_TestTable on TestTable for insert, update, delete as select p.[name] AS TableName from sysobjects p join sysobjects c on p.id = c.parent_obj where c.id = PROCID go update TestTable set attcol = 0 HTH Vern >-----Original Message----- >does anyone know how to obtain a trigger's owner table >within the trigger. >I want to generically get the name of the table that owns >the trigger, but without making ...

  1. #1

    Default A Trigger's Parent

    Howard:

    If you're using ERwin, you can use %TableName within the
    trigger definition.

    If you're not, the following sample should help:

    create table TestTable (keycol int PRIMARY KEY, attcol int)
    go

    create trigger iud_TestTable
    on TestTable
    for insert, update, delete
    as
    select p.[name] AS TableName
    from sysobjects p
    join sysobjects c
    on p.id = c.parent_obj
    where c.id = PROCID
    go

    update TestTable set attcol = 0

    HTH
    Vern

    >-----Original Message-----
    >does anyone know how to obtain a trigger's owner table
    >within the trigger.
    >I want to generically get the name of the table that owns
    >the trigger, but without making use of the trigger name
    or
    >id.
    > In other words, I want the trigger to know itself and
    the
    >table that invoked it.
    >.
    >
    Vern Rabe Guest

  2. #2

    Default Re: A Trigger's Parent

    Howard,

    SELECT object_name( procid ) as 'Triggername' would return the trigger
    name.To get the table name,afaik, there isnt a shortcut.If you are in
    SQL2000, you can write a UDF which will accept a trigger name and then query
    sysobjects to get parent object name or the table name, as in:

    SELECT [name]
    FROM sysobjects where ID IN (SELECT parent_obj
    FROM sysobjects WHERE
    [id]= OBJECT_ID(trigger_name))

    Then you can use the UDF in the trigger to get the table name too.So the
    code in trigger would be

    SELECT dbo.<udfname>(object_name( procid )) as 'tablename', object_name(
    procid ) as 'Triggername'


    --
    Dinesh.
    SQL Server FAQ at
    [url]http://www.tkdinesh.com[/url]

    "Howard Zager" <Howard.ZagerBarclaysCapital.com> wrote in message
    news:079501c340ad$d0354de0$a001280aphx.gbl...
    > does anyone know how to obtain a trigger's owner table
    > within the trigger.
    > I want to generically get the name of the table that owns
    > the trigger, but without making use of the trigger name or
    > id.
    > In other words, I want the trigger to know itself and the
    > table that invoked it.

    Dinesh.T.K Guest

  3. #3

    Default Re: A Trigger's Parent

    There is no such information available in SQL Server, you will have to hard
    code the information in the trigger.

    If you want to use this to log errors that occur in a trigger you can stop
    your work by the way. You can't handle errors in a trigger; any error in a
    trigger will terminate it and rollback the transaction that started it. That
    is because a trigger is implicitly part of a transaction, a transaction
    should have the ACID properties (Atomicity, Consistency, Isolation and
    Durability) and you don't really get consistency if you allow errors, that
    might go unhandled with the way error handling is implemented in SQL Server
    at the moment.

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Howard Zager" <Howard.ZagerBarclaysCapital.com> wrote in message
    news:079501c340ad$d0354de0$a001280aphx.gbl...
    > does anyone know how to obtain a trigger's owner table
    > within the trigger.
    > I want to generically get the name of the table that owns
    > the trigger, but without making use of the trigger name or
    > id.
    > In other words, I want the trigger to know itself and the
    > table that invoked it.

    Jacco Schalkwijk Guest

  4. #4

    Default Re: A Trigger's Parent

    This is a good start. procid is the id of the trigger. The trigger's row
    in sysobjects contains the id of the parent table in the parent_obj column.
    So you can then access the name of the object whose id is in parent_obj.
    There is no need for another function, and no need to ever access the name
    of the trigger, if all you want is the name of the parent object.

    create trigger mytrig on mytable for insert
    as select object_name(parent_obj) from sysobjects where id = procid

    This should return 'mytable'.


    --
    HTH
    ----------------
    Kalen Delaney
    SQL Server MVP
    [url]www.SolidQualityLearning.com[/url]


    "Dinesh.T.K" <tkdineshnospam.mail.tkdinesh.com> wrote in message
    news:ebURcaLQDHA.2852tk2msftngp13.phx.gbl...
    > Howard,
    >
    > SELECT object_name( procid ) as 'Triggername' would return the
    trigger
    > name.To get the table name,afaik, there isnt a shortcut.If you are in
    > SQL2000, you can write a UDF which will accept a trigger name and then
    query
    > sysobjects to get parent object name or the table name, as in:
    >
    > SELECT [name]
    > FROM sysobjects where ID IN (SELECT parent_obj
    > FROM sysobjects WHERE
    > [id]= OBJECT_ID(trigger_name))
    >
    > Then you can use the UDF in the trigger to get the table name too.So the
    > code in trigger would be
    >
    > SELECT dbo.<udfname>(object_name( procid )) as 'tablename', object_name(
    > procid ) as 'Triggername'
    >
    >
    > --
    > Dinesh.
    > SQL Server FAQ at
    > [url]http://www.tkdinesh.com[/url]
    >
    > "Howard Zager" <Howard.ZagerBarclaysCapital.com> wrote in message
    > news:079501c340ad$d0354de0$a001280aphx.gbl...
    > > does anyone know how to obtain a trigger's owner table
    > > within the trigger.
    > > I want to generically get the name of the table that owns
    > > the trigger, but without making use of the trigger name or
    > > id.
    > > In other words, I want the trigger to know itself and the
    > > table that invoked it.
    >
    >

    Kalen Delaney Guest

  5. #5

    Default A Trigger's Parent

    Thanks to everyone for your quick replies.
    >-----Original Message-----
    >does anyone know how to obtain a trigger's owner table
    >within the trigger.
    >I want to generically get the name of the table that owns
    >the trigger, but without making use of the trigger name
    or
    >id.
    > In other words, I want the trigger to know itself and
    the
    >table that invoked it.
    >.
    >
    Howard Zager Guest

Similar Threads

  1. Parent script calling another parent script
    By Mazuho in forum Macromedia Director 3D
    Replies: 1
    Last Post: May 31st, 07:20 PM
  2. XML.TRIGGER
    By Dan-C in forum Macromedia Flash Data Integration
    Replies: 1
    Last Post: February 28th, 03:55 PM
  3. Trigger
    By James in forum ASP Database
    Replies: 1
    Last Post: August 6th, 01:05 AM
  4. #24735 [NEW]: call_user_func on parent method does not use parent private property
    By tater at potatoe dot com in forum PHP Development
    Replies: 0
    Last Post: July 21st, 12:50 PM
  5. Trigger Help
    By Mike in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 9th, 07:28 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139