Path: csiph.com!x330-a1.tempe.blueboxinc.net!newsfeed.hal-mli.net!feeder3.hal-mli.net!newsfeed.hal-mli.net!feeder1.hal-mli.net!nx02.iad01.newshosting.com!newshosting.com!69.16.185.11.MISMATCH!npeer01.iad.highwinds-media.com!news.highwinds-media.com!feed-me.highwinds-media.com!postnews.google.com!da3g2000vbb.googlegroups.com!not-for-mail
From: Necoc Yaotl <tezcatlipoca1427@gmail.com>
Newsgroups: comp.databases.ms-sqlserver
Subject: Get trigger name inside trigger (reflection)
Date: Wed, 23 Nov 2011 20:32:18 -0800 (PST)
Organization: http://groups.google.com
Lines: 44
Message-ID: <e726eebf-685d-419d-90ce-39bbee3fe9c4@da3g2000vbb.googlegroups.com>
NNTP-Posting-Host: 76.71.203.69
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
X-Trace: posting.google.com 1322109262 10968 127.0.0.1 (24 Nov 2011 04:34:22 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Thu, 24 Nov 2011 04:34:22 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: da3g2000vbb.googlegroups.com; posting-host=76.71.203.69; posting-account=dilJyAoAAABjMyxKPvSAEQKZ-KJJgS1G
User-Agent: G2/1.0
X-Google-Web-Client: true
X-Google-Header-Order: HNKUARELSC
X-HTTP-UserAgent: Mozilla/5.0 (Windows NT 6.1) AppleWebKit/535.2 (KHTML, like Gecko) Chrome/15.0.874.121 Safari/535.2,gzip(gfe)
Xref: x330-a1.tempe.blueboxinc.net comp.databases.ms-sqlserver:832

Hi,

Inside a trigger, is there a way to get the name of that trigger? (in
SQL Server 2000).

Something like:

create trigger my_trigger on my_table for update
as

declare @current_trigger_name varchar(100)

select @current_trigger_name = some_function()

-- @current_trigger_name would contain 'my_trigger'
-- I am looking for 'some_function()' or any method to get the value


The problem that I am trying to solve is the following:

I need to implement a security mechanism based on application roles,
some roles will have have access to modify some fields of some tables,
some roles other fields in the same or other tables and so on. This
needs to be done in a web application ("classic" asp) that uses a
single sql user to connect to the database. The roles are controlled
at the application layer. I can't really modify the application layer
since it would involve touching hundreds of asp pages (yes, bad app
architecture), so I want to control the modifications at the database
level.

What I want to do is create a function or stored proc that will be
called in the 'update' trigger for every table that needs to be
controlled. The stored proc will basically get the table name (which I
can get after I have the trigger name) and user name as parameters and
will return the list of fields that the user is entitled to modify in
that table. The trigger will then check if any of the fields changed
and proceed or rollback the transaction as required.

I basically want to avoid having to put specific trigger/tables names
when I call my 'validation' proc in every trigger that I have to
touch.

Thanks