October 9, 2015 at 1:52 pm
Within a trigger, is there a way to get the trigger's name programmatically? Like a "Me.Name" type of construct? Access to a call stack would also work as well.
Thanks
--=Chuck
October 9, 2015 at 2:14 pm
This is from t-sql help and I believe it works with trigger objects too:
USE AdventureWorks2008R2;
GO
IF OBJECT_ID ( 'usp_FindName', 'P' ) IS NOT NULL
DROP PROCEDURE usp_FindName;
GO
CREATE PROCEDURE usp_FindName
@lastname varchar(40) = '%',
@firstname varchar(20) = '%'
AS
DECLARE @Count int;
DECLARE @ProcName nvarchar(128);
SELECT LastName, FirstName
FROM Person.Person
WHERE FirstName LIKE @firstname AND LastName LIKE @lastname;
SET @Count = @@ROWCOUNT;
SET @ProcName = OBJECT_NAME(@@PROCID);
RAISERROR ('Stored procedure %s returned %d rows.', 16,10, @ProcName, @Count);
GO
EXECUTE dbo.usp_FindName 'P%', 'A%';
October 9, 2015 at 2:27 pm
@@PROCID
Returns the object identifier (ID) of the current Transact-SQL module. A Transact-SQL module can be a stored procedure, user-defined function, or trigger.
https://msdn.microsoft.com/en-us/library/ms174408(v=sql.105).aspx
So this should work:
object_name( @@procid )
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 12, 2015 at 3:38 pm
Thanks! That's exactly what I was looking for.
Ask me if this is extending this question too far, and I'll ask in another thread ....
When I tried to implement the following code:
drop table mytable
go
create table mytable (id integer, name varchar(10))
go
create trigger mytable_ti on mytable
after insert
as
declare @myname varchar(10) = object_name( @@procid )
declare @sql varchar(100)
set @sql = 'update cur_t set name = ''Chuckers'' from '+ substring(@myname,1,len(@myname)-3) +' cur_t inner join inserted as i on i.id = cur_t.id'
--update cur_t set name = 'Chuckers' from mytable cur_t inner join inserted as i on i.id = cur_t.id
exec(@sql)
go
My insert statement fails, claiming the "Invalid object name 'inserted'." If you uncomment the line of code below "set @sql", and comment out the "exec" line (which is exactly the dynamic sql being generated), there is no error:
insert into mytable values (1, 'Chuck')
Why would the dynamically-generated SQL throw an error?
The reason why I'm attempting this is so that I can place some code common to every table in a procedure, which generates the SQL that can then be executed from within each table's trigger.
--=Chuck
October 13, 2015 at 4:06 am
The inserted and deleted pseudo-tables are only visible in the trigger's scope, not in any child scope (dynamic SQL, functions or procedures)
It looks like you're trying to write a trigger that you can use on any table. I strongly don't recommend that approach, it leads to overly-complex triggers and often slow triggers. If you want to write once and use the code in lots of triggers, write a procedure that, when run, uses dynamic SQL to create triggers on the required tables, with the created triggers specific to the table that they are created on
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 13, 2015 at 9:14 am
That was going to be my next tack, if this dynamic SQL approach wasn't going to work (or was a bad idea). This trigger code is ultimately meant to enforce modification_id/modification_date information. Since our standard on primary key naming means that all tables have one of two column names, if a developer copies and pastes the trigger code from one table to another and forgets to update the table name referenced in the trigger's update statement, then they could update another table's modification stamps.
--=cf
October 13, 2015 at 10:16 am
Rather than a dynamic trigger, you need dynamic code that generates a static trigger.
That is, after the table is created, run code that dynamically analyzes that table definition and generates the appropriate static (hard-coded) trigger. Trigger performance is critical, to minimize the time that the transaction is held open.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
October 13, 2015 at 10:29 am
Dynamic Sql is great for when you have a dynamic request. In this case I think the triggers should be fully static for best performance. My preference is for generating static triggers so that they don't have the possibility of the copy/paste error you are trying to avoid. If you have the case of not trusting developer code, then you have administrative issues.
An alternative might be to have all custom triggers with replaceable generated parts such as:
Create Trigger....
--tag:BeginAuditDate
replaceable update statement here
--tag:EndAuditDate
Rest of trigger...
In any case, an editor with good search/replace and RegEx handling will be your best friend.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply