What are the challenges you face when working across database platforms? Take the survey
Options

5.0.1.7 Smart Rename creates incorrect script

PDinCAPDinCA Posts: 642 Silver 1
edited March 16, 2011 4:33AM in SQL Prompt Previous Versions
Renaming a table that is only used in a trigger that posts updates to a same-named table in another, audit, database generates the change script without reference to the audit database and doesn't recognize the fact that I haven't also renamed the audit database's same-named table (OOPS!). I would have highlighted the missing database qualifier but using the "code" tag stops that...

Old Code:
ALTER TRIGGER [dbo].[trg_t_site_custom_def_AuditTrigger] ON [dbo].[t_site_custom_def]
AFTER DELETE,UPDATE
NOT FOR REPLICATION
AS 
BEGIN
	SET NOCOUNT ON;
	INSERT INTO [xcp_ref_audit].[dbo].[t_site_custom_def] ([custom_id],[custom_site_id],[custom_xml],[update_date],[update_by])
	SELECT [custom_id],[custom_site_id],[custom_xml],[update_date],[update_by] FROM Deleted
END
New Code:
ALTER TRIGGER dbo.trg_t_site_custom_def_AuditTrigger ON dbo.SiteCustom
AFTER DELETE,UPDATE
NOT FOR REPLICATION
AS 
BEGIN
	SET NOCOUNT ON;
	INSERT INTO dbo.SiteCustom ([custom_id],[custom_site_id],[custom_xml],[update_date],[update_by])
	SELECT [custom_id],[custom_site_id],[custom_xml],[update_date],[update_by] FROM Deleted
END
Jesus Christ: Lunatic, liar or Lord?
Decide wisely...

Comments

Sign In or Register to comment.