Trigger/Table/Function Issue

Question

Post reply

Trigger/Table/Function Issue

Douglas Bentley

Old Hand

Points: 355
More actions
August 22, 2002 at 7:12 am

#158296

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_