3

Imagine there are two databases named "DB1" and "DB2". There are two tables on both databases which are the same. I mean we have "Table1" in "DB1" and also "Table1" in "DB2" and the data is the same as well. I need to write a trigger so that whenever i change "Table1"'s name on "DB1", the name be changed automatically by a trigger on "DB2". thank you.

1
  • 1
    have you tried anything so far? Any specific obstacles you faced?
    – George K
    Commented Apr 3, 2017 at 10:52

1 Answer 1

2

Sys.Trigger_event_types will have all the events that can be fired.

On checking, it has an event type called RENAME. You could use that write a DDL trigger, though this may not be ideal but serves your purpose.

alter trigger  tst
on database
for
RENAME 
as 
begin

declare @oldtblname sysname,@newtablename sysname
  SELECT 
       @oldtblname= EVENTDATA().value  
        ('(/EVENT_INSTANCE/Parameters/Param)[1]','nvarchar(max)') ;

          SELECT 
       @newtablename= EVENTDATA().value  
        ('(/EVENT_INSTANCE/Parameters/Param)[2]','nvarchar(max)') ;

exec otherdbname.dbo.sp_rename @oldtblname,@newtablename;

end

There are some catches, since this is database level scoped event, this will fire for all rename events, you may want to check event type and if it is only <ObjectType>TABLE</ObjectType>, then you may wish to continue.

Note:
I have SQLServer 2012 and 2005 machines, this RENAME didn't worked on 2005 but worked on 2012. Not sure about 2008.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.