Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #832

Get trigger name inside trigger (reflection)

Show all headers | View raw

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

Back to comp.databases.ms-sqlserver | Previous | Next — Next in thread | Find similar

Thread

Get trigger name inside trigger (reflection) Necoc Yaotl <tezcatlipoca1427@gmail.com> - 2011-11-23 20:32 -0800
  Re: Get trigger name inside trigger (reflection) Erland Sommarskog <esquel@sommarskog.se> - 2011-11-24 08:14 +0000
    Re: Get trigger name inside trigger (reflection) Necoc Yaotl <tezcatlipoca1427@gmail.com> - 2011-11-24 18:04 -0800

csiph-web