April 8, 2002 at 11:21 am
I have a database where the datatype was changed from a string to an integer. I'm wanting to rename the field name appropriately in all related tables. In the main table where this occurs, the field is the PK. I was able to disable the update and delete triggers and change the field name thanks to an article here (Quickly Enabling and Disabling Constraints and Triggers) but when I try to update the triggers after the change, many of them give me an error message indicating a syntax problem (ODBC SQL State 42000, Error 170) near the trigger name. When I close it without saving it and open it back up, if I enter a space and then delete the space, it gives me the same error notice. How do I get this trigger to update properly? This is my first major SQL project- I've migrated an Access *.mdb to SQL Server 2000.
Tony L
SQL Server Rookie
Tony
April 8, 2002 at 11:47 am
Not sure of the problem. Can you post the table and trigger code. Also, the original table and alter code?
Steve Jones
April 8, 2002 at 3:02 pm
Here's the trigger on one of them:
CREATE TRIGGER "tblCustomerName_DTrig" ON dbo.tblCustomerName FOR DELETE AS
SET NOCOUNT ON
/* * CASCADE DELETES TO 'tblCustomerChallenge' */
DELETE tblCustomerChallenge FROM deleted, tblCustomerChallenge WHERE deleted.strCustID = tblCustomerChallenge.strCustID
/* * CASCADE DELETES TO 'tblCustomerPhoneNum' */
DELETE tblCustomerPhoneNum FROM deleted, tblCustomerPhoneNum WHERE deleted.strCustID = tblCustomerPhoneNum.strCustID
Here's the important basics on the tblCustomerName:
intCustID(int,4) changed from strCustID(nvarchar,20)
Other tables referencing the same field are
tblCustomerPhone and tblCustomerChallenge
I'm working at altering the code within the "Manage Triggers" dialog box found when I do a right click on the tblCustomerName.
Tony
Tony
April 8, 2002 at 3:32 pm
You may need to do it thru QA code by doing an
ALTER TRIGGER "tblCustomerName_DTrig" ON dbo.tblCustomerName FOR DELETE AS
SET NOCOUNT ON
/* * CASCADE DELETES TO 'tblCustomerChallenge' */
DELETE tblCustomerChallenge FROM deleted, tblCustomerChallenge WHERE deleted.intCustID = tblCustomerChallenge.intCustID
/* * CASCADE DELETES TO 'tblCustomerPhoneNum' */
DELETE tblCustomerPhoneNum FROM deleted, tblCustomerPhoneNum WHERE deleted.intCustID = tblCustomerPhoneNum.intCustID
It may be parsing the old create or trying to replace it. If this does not work can you be more specific about the error. You may also want to DROP the trigger and recreate them.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 8, 2002 at 4:29 pm
Thanks! The idea of deleting the old triggers and replacing them with new triggers worked.
Tony
Tony
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy