5

I need to pass the table name from trigger to procedure.

ALTER TRIGGER [dbo].[Trg_ProjectCreation] 
   ON  [dbo].[Projects] 
   AFTER INSERT
AS 
BEGIN   
    SET NOCOUNT ON; 
 -- procedure call 
    exec Proc_Test @Tablename (not need to hardcode)
END

I want to know is there any way I can get table name from trigger itself. I need this to be dynamic because when the table's name changes I don't want to change my code.

  • Why don't you just set the variable manually in the trigger? Is it because you are auto-generating the triggers? – RoKa May 1 '13 at 11:46
  • No, I want to write common logic in procedure for all table base on Information_Schema – KuldipMCA May 1 '13 at 11:53
  • 3
    But a trigger always belongs to exactly ony table (at least in SQL Server). So there is no point making the tablename dynamic (and the tablename is already "hardcoded" in the alter trigger part) – a_horse_with_no_name May 1 '13 at 12:22
  • @a_horse_with_no_name if somehow table name will change i need to change my code if I put table name "hardcoded". – KuldipMCA May 1 '13 at 12:58
  • 2
    and since you would need to change all references to that table why is that a problem? Personally it is rare to ever change a table name in a good design. If you are thinking about changing names, then you probably need to rethink that. – HLGEM May 3 '13 at 14:01
8

This might not be the best approach to dealing for your problem, but it will certainly achieve the stated goal.

ALTER TRIGGER [dbo].[Trg_ProjectCreation] ON  [dbo].[Projects] 
   AFTER INSERT
AS 
BEGIN   
    SET NOCOUNT ON; 
    declare @TableName sysname
    select @tablename = object_schema_name(parent_id) + '.' + object_name(parent_id) 
    from sys.triggers where object_id = @@PROCID

    exec dbo.myProcedure @Tablename 
END
GO

A better option may be to change the way the trigger is created, and putting the name down manually (if it fits your scenario), for e.g.:

declare @tablename sysname, @sql nvarchar(max)
set @tablename = '[dbo].[myTableName]'
set @sql = 'ALTER TRIGGER [dbo].[Trg_ProjectCreation] ON  ' + @tablename + ' 
   AFTER INSERT
AS 
BEGIN   
    SET NOCOUNT ON; 
    exec dbo.myProcedure ' + @tablename + '
END
GO'
exec sp_executeSQL @sql

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.