CODEWITHSUNDEEP
sqlsql-serversql-server-2005triggers
jsr
jsr

Reputation:

Sql Trigger - which table does it belong to?

Is there a way within a Sql Server 2005 Trigger to get the name and schema of the table that the trigger is attached to during execution?

Upvotes: 4

Views: 9049

Answers (2)

Jhonny D. Cano -Leftware-
Jhonny D. Cano -Leftware-

Reputation: 18013

This is a dirty way to know it

SELECT o.name
FROM sysobjects t
JOIN sysobjects o ON t.parent_obj = o.id
WHERE t.name = 'your_trigger_name'

[EDIT]

According to the other answer and the comments, i think this can fit to you (MSSQL2000 version)

SELECT o.name
FROM sysobjects t
JOIN sysobjects o ON t.parent_obj = o.id
WHERE t.id = @@PROCID

Upvotes: 1

gbn
gbn

Reputation: 432180

SELECT
    OBJECT_NAME(parent_id) AS [Table],
    OBJECT_NAME(object_id) AS TriggerName
FROM
    sys.triggers
WHERE
    object_id = @@PROCID

Then you can also use OBJECTPROPERTY to get extra info, such as after/before, delete/insert/update, first/last etc

Upvotes: 13

Related Questions