Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

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.

share|improve this question
Why don't you just set the variable manually in the trigger? Is it because you are auto-generating the triggers? – RoKa May 1 at 11:46
No, I want to write common logic in procedure for all table base on Information_Schema – KuldipMCA May 1 at 11:53
2  
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 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 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 at 14:01
show 1 more comment

1 Answer

up vote 4 down vote accepted

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
share|improve this answer
Thanks first option will work for me,Thanks a lot. – KuldipMCA May 1 at 13:04

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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