How to Write a Trigger to Change Table Name Automatically in SQL Server

sql server

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.

Best Answer

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.

Related Solutions

Sql-server – Looking for a Trigger to change inserting strings

if you want to use triggers thats your call, i am just suggesting a different approach

you can use the Default Value or Binding feature of a table

e.g in your table1, you can make the default value of Name column = 'Sample Name' by below script

ALTER TABLE table1 ADD CONSTRAINT
    DF_Table1_Name DEFAULT N'Sample Name' FOR Name
GO

Look at image below Table Default value of Column

This code below adds a trigger to your table that works after insert, it updates the name added and replaces the 'x' with '*'

CREATE TRIGGER ReplacingTrigger 
ON  table1  
AFTER INSERT 
AS  
BEGIN 
    UPDATE table1
    SET table1.name = replace(table1.name,'x','*')
    FROM table1
    INNER JOIN inserted i on i.name = table1.name
END

i suggest if you have an ID in the table to use it in the joins to find the exact record inserted to make it accurate like below

CREATE TRIGGER ReplacingTrigger 
ON  table1  
AFTER INSERT 
AS  
BEGIN 
    UPDATE table1
    SET table1.name = replace(table1.name,'x','*')
    FROM table1
    INNER JOIN inserted i on i.id= table1.id
END

SQL Server – Schema Unusable After Deleting Another Database

It looks like you'll need to change the user's default database, as it appears this is currently set to DB1, which no longer exists.

exec sp_defaultdb @loginame='user1', @defdb='DB2'

ALTER LOGIN <login_name> WITH DEFAULT_DATABASE = <default_database>

Best Answer

Related Solutions

Sql-server – Looking for a Trigger to change inserting strings

SQL Server – Schema Unusable After Deleting Another Database

Related Question