Trigger Syntax Error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Trigger Syntax Error

Author

Topic

Scull
Starting Member

5 Posts

Posted - 2003-05-29 : 16:46:10

Hello - I have written a trigger that is supposed to do the following...
Update one field within a table based on data being posted to that table. That is, column IDACCTSET should be updated with the last 3 characters from IDAPACCT when the data is either inserted or updated.
My trans-sql statement is shown below along with the error statement.
Thanks in advance for your help!!! Rich

CREATE TRIGGER FixAPATRPayments
ON APATR
FOR INSERT, UPDATE
AS
Declare @Entity char
SELECT @Entity = SUBSTRING(I.IDAPACCT,CHARINDEX('-',RTRIM(I.IDAPACCT)),3)
FROM APATR A, INSERTED I
WHERE A.IDINVC = I.IDINVC
AND A.IDVEND = I.IDVEND

BEGIN
SET IDACCTSET = @Entity
RAISERROR ('Please contact your database administrator', 16, 1)
ROLLBACK TRANSACTION
END

GO

------------------------------------------------------
Server: Msg 170, Level 15, State 1, Procedure FixAPATRPayments, Line 14
Line 14: Incorrect syntax near '='.

jung1975
Aged Yak Warrior

503 Posts

Posted - 2003-05-29 : 17:03:29

try...

If exists ( Your select stament here)

BEGIN
RAISERROR ('Please contact your database administrator', 16, 1)
ROLLBACK TRANSACTION
END

nr
SQLTeam MVY

12543 Posts

Posted - 2003-05-29 : 17:09:21

SET IDACCTSET = @Entity
This is invalid.
should be

update APATR
set IDACCTSET = @Entity
from inserted
where FixAPATRPayments.pk = inserted.PK

In fact you can right the trigger as

CREATE TRIGGER FixAPATRPayments
ON APATR
FOR INSERT, UPDATE
AS
update APATR
set IDACCTSET = SUBSTRING(I.IDAPACCT,CHARINDEX('-',RTRIM(I.IDAPACCT)),3)
from inserted
where FixAPATRPayments.pk = inserted.PK
go

And it will cater for multi row updates/inserts.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Scull
Starting Member

5 Posts

Posted - 2003-05-29 : 19:59:22

To Jung... when I tried that I got the following error, along with my code now:
---------------------
CREATE TRIGGER FixAPATRPayments
ON APATR
FOR INSERT, UPDATE
AS
Declare @Entity char
If Exists (SELECT @Entity = SUBSTRING(I.IDAPACCT,CHARINDEX('-',RTRIM(I.IDAPACCT)),3)
FROM APATR A, INSERTED I
WHERE A.IDINVC = I.IDINVC
AND A.IDVEND = I.IDVEND)

BEGIN
SET IDACCTSET = @Entity
RAISERROR ('Please contact your database administrator', 16, 1)
ROLLBACK TRANSACTION
END

GO

--------------------------
Server: Msg 170, Level 15, State 1, Procedure FixAPATRPayments, Line 6
Line 6: Incorrect syntax near '='.
Server: Msg 170, Level 15, State 1, Procedure FixAPATRPayments, Line 12
Line 12: Incorrect syntax near '='.
-------------------------

Any thoughts? Thanks for your feedback!

Scull
Starting Member

5 Posts

Posted - 2003-05-29 : 20:01:41

To nr... when I tried your's I got the following error, also along with my code here:
----------------
CREATE TRIGGER FixAPATRPayments
ON APATR
FOR INSERT, UPDATE
AS
update APATR
set IDACCTSET = SUBSTRING(I.IDAPACCT,CHARINDEX('-',RTRIM(I.IDAPACCT)),3)
from Inserted I
where FixAPATRPayments.pk = Inserted.PK
go
-------------------

Server: Msg 107, Level 16, State 2, Procedure FixAPATRPayments, Line 5
The column prefix 'FixAPATRPayments' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Procedure FixAPATRPayments, Line 5
The column prefix 'Inserted' does not match with a table name or alias name used in the query.

--------------------

Thanks for your feedback! Any other thoughts would be greatly appreciated!

Scull
Starting Member

5 Posts

Posted - 2003-06-02 : 07:59:33

bump... please help

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-06-02 : 10:00:49

I think this should fix it:

CREATE TRIGGER FixAPATRPayments
ON APATR
FOR INSERT, UPDATE
as
update APATR
set IDACCTSET = SUBSTRING(I.IDAPACCT,CHARINDEX('-',RTRIM(I.IDAPACCT)),3)
from Inserted I inner join APATR
on APATR.pk = I.pk
go

Substitute the pk with the appropriate primary key/foriegn key column names.

Owais

Scull
Starting Member

5 Posts

Posted - 2003-06-03 : 09:29:12

That did it!!! Thanks for all your help. I might be asking help later on another trigger as well if I can't figure it out. I'm hoping using the information learned here that I can figure it out myself. We'll see...

Thanks again!
Rich

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources