i've troubleshot many trigger sql syntax errors , but this time i am stumped
.... probably something very simple i'm overlooking ....


the QA error is

Server: Msg 170, Level 15, State 1, Procedure TriggerChangeHistoryAddress,
Line 100
Line 100: Incorrect syntax near ','.
Server: Msg 170, Level 15, State 1, Procedure TriggerChangeHistoryAddress,
Line 170
Line 170: Incorrect syntax near ','.



the t-sql is :


if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[TriggerChangeHistoryAddress]') and OBJECTPROPERTY(id,
N'IsTrigger') = 1)
drop trigger [dbo].[TriggerChangeHistoryAddress]
GO

CREATE TRIGGER dbo.TriggerChangeHistoryAddress
ON dbo.Address
AFTER UPDATE, INSERT, DELETE
AS

DECLARE @ChangeID int
DECLARE @ChangeSequence char(1)

DECLARE @AddressID int
DECLARE @LicenseID int
DECLARE @AmendmentID int
DECLARE @StreetForeign bit
DECLARE @StreetAddr1 varchar(100)
DECLARE @StreetAddr2 varchar(100)
DECLARE @StreetCity varchar(50)
DECLARE @StreetState varchar(2)
DECLARE @StreetZipcode varchar(9)
DECLARE @MailForeign bit
DECLARE @MailAddr1 varchar(100)
DECLARE @MailAddr2 varchar(100)
DECLARE @MailCity varchar(50)
DECLARE @MailState varchar(2)
DECLARE @MailZipcode varchar(9)
DECLARE @CreatedBy varchar(25)
DECLARE @DateCreated datetime
DECLARE @ModifiedBy varchar(25)
DECLARE @DateModified datetime

SET @AmendmentID = 0

SELECT @ChangeID = COALESCE(MAX(ChangeID),0) + 1 FROM ChangeHistoryAddress

SET @ChangeSequence = 'B'


SELECT @AddressID=AddressID,
@LicenseID=LicenseID,
@StreetForeign=StreetForeign,
@StreetAddr1=StreetAddr1,
@StreetAddr2=StreetAddr2,
@StreetCity=StreetCity,
@StreetState=StreetState,
@StreetZipcode=StreetZipcode,
@MailForeign=MailForeign,
@MailAddr1=MailAddr1,
@MailAddr2=MailAddr2,
@MailCity=MailCity,
@MailState=MailState,
@MailZipcode=MailZipcode,
@CreatedBy=CreatedBy,
@DateCreated=DateCreated,
@ModifiedBy=ModifiedBy,
@DateModified=DateModified
from deleted


INSERT INTO ChangeHistoryAddress
(ChangeID,
ChangeSequence,
AmendmentID,
AddressID,
LicenseID,
StreetForeign,
StreetAddr1,
StreetAddr2,
StreetCity,
StreetState,
StreetZipcode,
MailForeign,
MailAddr1,
MailAddr2,
MailCity,
MailState,
MailZipcode,
CreatedBy,
DateCreated,
ModifiedBy,
DateModified,
ChangeHistoryDateCreated)
values
(@ChangeID,
@ChangeSequence,
0,
COALESCE(@AddressID,0),
COALESCE(@LicenseID,0),
COALESCE(@StreetForeign,0),
COALESCE(@StreetAddr1,''),
COALESCE(@StreetAddr2,''),
COALESCE(@StreetCity, ''),
COALESCE(@StreetState,''),
COALESCE(@StreetZipcode,''),
COALESCE(@MailForeign,0),
COALESCE(@MailAddr1,''),
COALESCE(@MailAddr2,''),
COALESCE(@MailCity,''),
COALESCE(@MailState,''),
COALESCE(@MailZipcode,''),
COALESCE(@CreatedBy,'audit'),
COALESCE(@DateCreated,,getdate()),
COALESCE(@ModifiedBy,'audit'),
COALESCE(@DateModified,getdate()),
GETDATE()
)

SET @ChangeSequence = 'A'

SELECT @AddressID=AddressID,
@LicenseID=LicenseID,
@StreetForeign=StreetForeign,
@StreetAddr1=StreetAddr1,
@StreetAddr2=StreetAddr2,
@StreetCity=StreetCity,
@StreetState=StreetState,
@StreetZipcode=StreetZipcode,
@MailForeign=MailForeign,
@MailAddr1=MailAddr1,
@MailAddr2=MailAddr2,
@MailCity=MailCity,
@MailState=MailState,
@MailZipcode=MailZipcode,
@CreatedBy=CreatedBy,
@DateCreated=DateCreated,
@ModifiedBy=ModifiedBy,
@DateModified=DateModified
from inserted

INSERT INTO ChangeHistoryAddress
(ChangeID,
ChangeSequence,
AmendmentID,
AddressID,
LicenseID,
StreetForeign,
StreetAddr1,
StreetAddr2,
StreetCity,
StreetState,
StreetZipcode,
MailForeign,
MailAddr1,
MailAddr2,
MailCity,
MailState,
MailZipcode,
CreatedBy,
DateCreated,
ModifiedBy,
DateModified,
ChangeHistoryDateCreated)
values
(@ChangeID,
@ChangeSequence,
0,
COALESCE(@AddressID,0),
COALESCE(@LicenseID,0),
COALESCE(@StreetForeign,0),
COALESCE(@StreetAddr1,''),
COALESCE(@StreetAddr2,''),
COALESCE(@StreetCity, ''),
COALESCE(@StreetState,''),
COALESCE(@StreetZipcode,''),
COALESCE(@MailForeign,0),
COALESCE(@MailAddr1,''),
COALESCE(@MailAddr2,''),
COALESCE(@MailCity,''),
COALESCE(@MailState,''),
COALESCE(@MailZipcode,''),
COALESCE(@CreatedBy,'audit'),
COALESCE(@DateCreated,,getdate()),
COALESCE(@ModifiedBy,'audit'),
COALESCE(@DateModified,getdate()),
GETDATE()
)


GO

You have a double comma on this line:
COALESCE(@DateCreated,,getdate()),

And you've copy/pasted the same error on to another line a little later on.

Does this help?

Regards,
Colin.


John A Grandy wrote:
[Large amount of SQL omitted for brevity]

--
- Developer Services Provider: http://wdevs.com
For Blogs, FTP, Mail, Forums, Members Product Areas
- My Blog: http://blogs.wdevs.com/ColinAngusMackay

COALESCE(@DateCreated,,getdate()),

You have 1 too many commas in both insets.Can see it from a mile.

Your trigger assumes that only 1 row gets affected at once, serious
limitation you are building in. Why are you selecting into variables and
then inserting?

Do a:

INSERT xxx
(
yyy,
......
)
SELECT
cols,
....
FROM INSERTED (or DELETED)

Regards
--------------------------------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: mike (AT) epprecht (DOT) net

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

"John A Grandy" <johnagrandy-at-yahoo-dot-com> wrote

If you double-click the error it will take you to the problem line. You have
extra comas in there.

Why are you writing triggers this way? As I explained in my previous
response you should allow for operations that affect multiple rows. It's
much simpler and better to write:

INSERT INTO ChangeHistoryAddress
(changesequence, amendmentid, addressid, licenseid,
streetforeign, streetaddr1, streetaddr2, streetcity, streetstate,
streetzipcode, mailforeign, mailaddr1, mailaddr2, mailcity,
mailstate, mailzipcode, createdby, datecreated, modifiedby,
datemodified, changehistorydatecreated)
SELECT
'A'
COALESCE(addressid,0),
COALESCE(licenseid,0),
COALESCE(streetforeign,0),
COALESCE(streetaddr1,''),
COALESCE(StreetAddr2,''),
... etc...
FROM Inserted

No need to declare all those variables.

If you really think you need a "changeid" (whatever that is for) then use
IDENTITY. Your method just won't work as a surrogate key.

Hope this helps.

--
David Portas
SQL Server MVP
--

but with an Identity, the 'B' ('BEFORE') and 'A' ('AFTER') row-pairs won't
match up ...

With an identity, I'll get

1B
2A
3B
4A
etc

when what I want is

1B
1A
2B
2A




"David Portas" <REMOVE_BEFORE_REPLYING_dportas (AT) acm (DOT) org> wrote

i don't find that double-clicking the errror consistently takes me to the
trouble line ... maybe the general vicinity ... but not the exact line ...


"David Portas" <REMOVE_BEFORE_REPLYING_dportas (AT) acm (DOT) org> wrote

By preserving both the Before and After versions of the row you are creating
redundant data in your History table. The Before version already exists as
the After of the previous transaction. You can match them on the datetime
sequence. Do that and you shouldn't need the artificial key Changeid and
Changesequence.

--
David Portas
SQL Server MVP
--