Trigger/Table/Function Issue
-
I have written a scripts that creates a table, function and trigger to be used together for an autoimport. However whenever I attempt to create the trigger it fails:Server: Msg 207, Level 16, State 3, Procedure _EXCHANGE_ins, Line 64
Invalid column name 'PHONE'.
I have rechecked and rechecked but cannot find the error, so I removed the phone from the table and trigger. This works, but I still need it. So I changed column to TPHONE and get the samething.
Any Ideas?
Table:
/****** Object: Table dbo._EXCHANGE_ Script Date: 8/20/02 10:32:52 AM ******/
if exists (select * from sysobjects where id = object_id('dbo._EXCHANGE_') and sysstat & 0xf = 3)
drop table dbo._EXCHANGE_
GO
CREATE TABLE dbo._EXCHANGE_ (
CLIENT varchar (15) NULL,
FNAME varchar (25) NULL ,
LNAME varchar (30) NULL ,
COMPANY_ID varchar (30) NULL,
COMPANY INT NULL,
DEPT INT NULL,
DEPT_ID varchar (30) NULL,
PHONE varchar (13) NULL,
EXTENSION varchar (5) NULL,
POSITION varchar (50) NULL
)
GO
GRANT SELECT , INSERT , DELETE , UPDATE ON dbo._EXCHANGE_ TO _SMDBA_
GO
/***Last name
First Name= FNAME
Alias Name (CLIENTID)=CLIENT
Office (This is the Company table)=COMPANY
Division (THIS IS THE DEPT ID)
Phone Number=PHONE
Personnel Number=EXTENSION
***/
Trigger:
/****** Object: Trigger dbo._EXCHANGE_ins Script Date: 7/25/01 11:43:06 AM ******/
if exists (select * from sysobjects where id = object_id('dbo._EXCHANGE_ins') and sysstat & 0xf = 8)
drop trigger dbo._EXCHANGE_ins
GO
CREATE trigger dbo._EXCHANGE_ins
on dbo._EXCHANGE_
for insert
as
DECLARE@intCNT INT,@intTMP INT,@varBDATE varchar(11),@varLUS varchar(25),@varGRP varchar(15),
@CLIENT varchar (15),
@FNAME varchar (25),
@LNAME varchar (30),
@COMPANY_ID varchar (30),
@COMPANY INT,
@DEPT_ID varchar (30),
@DEPT INT,
@PHONE varchar (13),
@EXTENSION varchar (5),
@POSITION varchar (50)
SET @intCNT = (SELECT ISNULL(COUNT(0),0) FROM dbo._EXCHANGE_)
DECLARE a_cursor CURSOR FOR SELECT
@intCNT as SEQUENCE,
CONVERT(varchar,YEAR(getdate()))+'-'+CONVERT(varchar,MONTH(getdate()))+'-'+CONVERT(varchar,DAY(getdate()))as LASTMODIFIED,
'AUTO IMPORT'as LASTUSER,
'1'as _GROUP_,
CLIENT,
FNAME
LNAME,
COMPANY_ID,
COMPANY,
DEPT_ID,
DEPT,
PHONE,
EXTENSION,
POSITION
FROM dbo._EXCHANGE_
OPEN a_cursor
WHILE @intCNT <> 0
BEGIN
FETCH NEXT
FROM a_cursor INTO
@intTMP,@varBDATE,@varLUS,@varGRP,
@CLIENT,@FNAME,@LNAME, @PHONE, @EXTENSION, @POSITION
SET @COMPANY=(SELECT MAX(SEQUENCE) FROM _SMDBA_._COMPANY_ WHERE "NAME"=@COMPANY_ID)
SET @DEPT=(SELECT MAX(SEQUENCE) FROM _SMDBA_._DEPART_ WHERE DEPT=@DEPT_ID)
IF (SELECT ISNULL(COUNT(0),0) FROM _SMDBA_._CUSTOMER_ WHERE CLIENT =RTRIM( @CLIENT)) = 1
UPDATE _SMDBA_._CUSTOMER_ SET CLIENT=UPPER(RTRIM(@CLIENT)),
FNAME = LTRIM(RTRIM(@FNAME)),
"NAME" = LTRIM(RTRIM(@LNAME)),
PHONE=dbo.USPHONE(@PHONE),
EXT=LTRIM(RTRIM(@EXTENSION)),
COMPANY=(SELECT MAX(SEQUENCE) FROM _SMDBA_._COMPANY_ WHERE "NAME"=@COMPANY_ID),
DEPT = (SELECT MAX(SEQUENCE) FROM _SMDBA_._DEPART_ WHERE DEPT=@DEPT_ID),
POSITION = LTRIM(RTRIM(@POSITION))
WHERE CLIENT = UPPER(RTRIM(@CLIENT))
ELSE
INSERT INTO _SMDBA_._CUSTOMER_
("SEQUENCE", CLIENT, FNAME, "NAME", PHONE, EXT, COMPANY, DEPT, NJNM_POSITION)
SELECT (SELECT RECNUM + 1 FROM dbo.SMSYSRECNUM WHERE "NAME" = '_CUSTOMER_'),
CLIENT=UPPER(RTRIM(@CLIENT)),
FNAME = LTRIM(RTRIM(@FNAME)),
"NAME" = LTRIM(RTRIM(@LNAME)),
PHONE= dbo.USPHONE(PHONE),
EXT=LTRIM(RTRIM(@EXTENSION)),
COMPANY=(SELECT MAX(SEQUENCE) FROM _SMDBA_._COMPANY_ WHERE CODE = @COMPANY_ID),
DEPT = (SELECT MAX(SEQUENCE) FROM _SMDBA_._DEPART_ WHERE DEPT=@DEPT_ID),
NJNM_POSITION = LTRIM(RTRIM(@POSITION))
UPDATE dbo.SMSYSRECNUM SET RECNUM = RECNUM + 1 WHERE "NAME" = '_CUSTOMER_'
SET @intCNT = @intCNT - 1
END
CLOSE a_cursor
DEALLOCATE a_cursor
DELETE FROM dbo._EXCHANGE_
-
The error is caused by the insert statement in the _CUSTOMER_ table. It reads :
PHONE = dbo.USPHONE(PHONE)
You forgot the @ ...
-
There is alot to be said for a second pair of eyes.
Thanks so much.
Doug
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply