CODEWITHSUNDEEP
sqlt-sql
Ryan Hargreaves
Ryan Hargreaves

Reputation: 277

Is it possible to obtain information about a trigger within the trigger execution?

I'm looking to create a table that I can use for overriding trigger execution (so, turn them on and off individually according to a database value). I was interested in doing this by using table name and trigger name, to account for multiple triggers, which lead me to a question of interest; is it possible to obtain information about a trigger, such as its name and the table its executing on, within the execution of the trigger?

Upvotes: 1

Views: 232

Answers (1)

Razvan Socol
Razvan Socol

Reputation: 5684

In SQL Server you can use the @@PROCID and OBJECT_NAME functions to get the name of the trigger (or stored procedure) which is executing. For example:

CREATE TABLE MyTable (SomeColumn INT)

GO
CREATE TRIGGER MyTrigger ON dbo.MyTable 
FOR INSERT
AS
PRINT OBJECT_NAME(@@PROCID)
DECLARE @TableName sysname
SET @TableName=(SELECT OBJECT_NAME(parent_object_id) FROM sys.objects WHERE object_id=@@PROCID)
PRINT @TableName

GO
INSERT INTO dbo.MyTable (SomeColumn) VALUES (1)

Upvotes: 2

Related Questions