SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 DDL trigger to rename table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

chase88
Starting Member

6 Posts

Posted - 05/09/2013 :  12:57:40  Show Profile  Reply with Quote
Hello:
A user exports a table using Access v2010 to our SQL Server 2008 R2 via ODBC. When this happens I need to rename the current table with _OLD (BC_MASTER --> BC_MASTER_OLD), and then rename the new table from Access with the standard name (BC_MASTER_NEW --> BC_MASTER). To automate this, I created a DDL trigger with the CREATE_TABLE event which fires when the table from Access is made in the schema and the first rename (BC_MASTER --> BC_MASTER_OLD) works. But the second rename (BC_MASTER_NEW --> BC_MASTER) gives an error saying "Could not execute query; could not find linked table". How can I rename that new table? Is it because the table name that fires the trigger is referenced in the trigger and you can't access that table name? Here's the trigger:

----------

CREATE TRIGGER BC_1
ON DATABASE
FOR CREATE_TABLE
AS
IF EXISTS(SELECT sobjects.name
FROM sysobjects sobjects
WHERE sobjects.xtype = 'U' and name='BC_MASTER_NEW')
BEGIN
EXEC sp_rename 'BC_MASTER', 'BC_MASTER_OLD';
EXEC sp_rename 'BC_MASTER_NEW', 'BC_MASTER';
END

MuMu88
Aged Yak Warrior

547 Posts

Posted - 05/09/2013 :  13:39:06  Show Profile  Reply with Quote
Here is a way to overcome your dilemma (there might be better ways):
Create two triggers as follows:


CREATE TRIGGER BC_1 
ON DATABASE 
FOR CREATE_TABLE 
AS 
IF EXISTS(SELECT sobjects.name 
FROM sysobjects sobjects 
WHERE sobjects.xtype = 'U' and name='BC_MASTER_NEW') 
BEGIN 
EXEC sp_rename 'BC_MASTER', 'BC_MASTER_OLD';  
END



CREATE TRIGGER BC_2 
ON DATABASE 
FOR RENAME 
AS 
IF EXISTS(SELECT sobjects.name 
FROM sysobjects sobjects 
WHERE sobjects.xtype = 'U' and name='BC_MASTER_OLD') 
BEGIN 
EXEC sp_rename 'BC_MASTER_NEW', 'BC_MASTER';   
END
Go to Top of Page

chase88
Starting Member

6 Posts

Posted - 05/10/2013 :  08:36:46  Show Profile  Reply with Quote
@MuMu88:

thanks for your reply. Unfortunately, I ran your solution but received the error "Could not execute query; could not find linked table. Invalid object name 'BC_MASTER_NEW' ". If I disable BC_2 then BC_1 works alone: I get a BC_MASTER_NEW and a BC_MASTER_OLD table. So I just need to get BC_MASTER_NEW renamed to BC_MASTER. Any other ideas?
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 05/10/2013 :  11:47:17  Show Profile  Reply with Quote
I was able to create & execute the two triggers successfully, here is my code and associated results:


IF OBJECT_ID('BC_1') IS NOT NULL DROP TRIGGER BC_1 ON DATABASE;
GO

ALTER TRIGGER BC_1 
ON DATABASE 
FOR CREATE_TABLE 
AS 
IF EXISTS(SELECT sobjects.name 
FROM sysobjects sobjects 
WHERE sobjects.xtype = 'U' and name='TEMP_NEW') 
BEGIN 
EXEC sp_rename 'TEMP', 'TEMP_OLD';  
PRINT 'EXECUTED TRIGGER1'  
END
GO

IF OBJECT_ID('BC_2') IS NOT NULL DROP TRIGGER BC_2 ON DATABASE;
GO

ALTER TRIGGER BC_2 
ON DATABASE 
FOR RENAME 
AS 
IF EXISTS(SELECT sobjects.name 
FROM sysobjects sobjects 
WHERE sobjects.xtype = 'U' and name='TEMP_OLD') 
BEGIN 
EXEC sp_rename 'TEMP_NEW', 'TEMP'; 
PRINT 'EXECUTED TRIGGER2'  
END

CREATE TABLE TEMP
(ID INT, FOO CHAR(10));

CREATE TABLE TEMP_NEW 
(ID INT, FOO CHAR(10));

AS SOON AS THE TEMP_NEW TABLE IS CREATED I GET THE FOLLOWING RESULTS:
Caution: Changing any part of an object name could break scripts and stored procedures.
Caution: Changing any part of an object name could break scripts and stored procedures.
EXECUTED TRIGGER2
EXECUTED TRIGGER1


DROP TABLE TEMP;
DROP TABLE TEMP_OLD;

IF OBJECT_ID('BC_2') IS NOT NULL DROP TRIGGER BC_2 ON DATABASE;

IF OBJECT_ID('BC_1') IS NOT NULL DROP TRIGGER BC_1 ON DATABASE;





Would you be able to post error messages you are getting...
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 05/10/2013 :  12:47:05  Show Profile  Reply with Quote
Maybe it would be better to take a step back and describe your current situation in more detail and the goal you are trying to get to.

You mentioned that the user exports a table form Access to SQL. What does that mean? Is Access creating a table in SQL or is there an existing table that the data is copied into?

The reason I ask is that this seems like a rather kludgy implementation and maybe there is a better wat to handle it.
Go to Top of Page

chase88
Starting Member

6 Posts

Posted - 05/10/2013 :  13:27:53  Show Profile  Reply with Quote
I ran your code and got the same results as you did:

Caution: Changing any part of an object name could break scripts and stored procedures.
Caution: Changing any part of an object name could break scripts and stored procedures.
EXECUTED TRIGGER2
EXECUTED TRIGGER1

Then for the next test, from inside Access using ODBC I exported a file named TEMP_NEW to SQL Server instead of creating the table in SSMS and got the following error:

Could not execute query; could not find linked table.
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'TEMP_NEW', (#208) [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. (#8180).

So your code works when the TEMP_NEW is created in SSMS, but not for an imported file from Access. Have you tried using an imported file from Access?
Go to Top of Page

chase88
Starting Member

6 Posts

Posted - 05/10/2013 :  13:44:56  Show Profile  Reply with Quote
quote:
Originally posted by Lamprey

Maybe it would be better to take a step back and describe your current situation in more detail and the goal you are trying to get to.

You mentioned that the user exports a table form Access to SQL. What does that mean? Is Access creating a table in SQL or is there an existing table that the data is copied into?

The reason I ask is that this seems like a rather kludgy implementation and maybe there is a better wat to handle it.



@Lamprey:
A Classic ASP app reads/writes to the BC_MASTER sql table, the owner of this app wants to be able to download BC_MASTER to Access, make edits and clean up data, then export it back to SQL Server, this creates a new table in sql server with a _NEW appended in the name. So I set up an ODBC connection in Access and saved the import/export steps so that the owner can easily import/export the table to SQL Server. The import/export work fine, but to avoid having him tell me that he put a new file on SQL Server and me then logon SQL Server and manually rename the file, I am trying to automate the rename by using a trigger(s). I agree it's kludgy.
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 05/10/2013 :  15:30:28  Show Profile  Reply with Quote

I am dealing with some of my own ODBC driver issues... So I can’t test these triggers using Access at this moment... :-(

There might be some relavant information at these sites:
http://support.microsoft.com/kb/238116
http://windowssecrets.com/forums/showthread.php/60022-SQL-Linked-Table-Error-(2000)

Good luck...
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 05/10/2013 :  15:50:49  Show Profile  Reply with Quote
Then I guess I'm curious what the goal/business reasons are?

Is it really necessary to have NEW and OLD tables? Would it be enough to have the table in access "merge" back into the BC_MASTER? Is there some sort of auditability that they are trying to achieve?

I'm not saying your client can't have what they want, if they want to do something silly. But, understanding the goal is probably more important than us trying to help you implement a kludge. On the other hand, I've had clients that want some really silly things no matter how much you tell them it'll be more expensive, less perfornant, fragile and not as helpful as other solutions.
Go to Top of Page

chase88
Starting Member

6 Posts

Posted - 05/13/2013 :  07:15:59  Show Profile  Reply with Quote
quote:
Originally posted by Lamprey

Then I guess I'm curious what the goal/business reasons are?

Is it really necessary to have NEW and OLD tables? Would it be enough to have the table in access "merge" back into the BC_MASTER? Is there some sort of auditability that they are trying to achieve?

I'm not saying your client can't have what they want, if they want to do something silly. But, understanding the goal is probably more important than us trying to help you implement a kludge. On the other hand, I've had clients that want some really silly things no matter how much you tell them it'll be more expensive, less perfornant, fragile and not as helpful as other solutions.



@Lamprey:
Yes they wanted to create an audit trail. I could still create the OLD and then merge the Access table into BC_MASTER. How would you "merge" the Access table into SQL Server table BC_MASTER like you suggested?
Go to Top of Page

chase88
Starting Member

6 Posts

Posted - 05/23/2013 :  13:24:08  Show Profile  Reply with Quote
I ended up not using the trigger approach. Instead I created an ODBC SQL Pass-Through Query in Access. So after the user exports the new table, they click the saved query which runs a stored proc on sql server to rename the tables. No problems or errors.
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 05/23/2013 :  20:35:30  Show Profile  Reply with Quote
Thanks for sharing the information.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000