What is wrong with this procedure?

[signaturefactory]

I get the following error for this procedure:
Server: Msg 156, Level 15, State 1, Procedure Trig_Ins_TrackingNumbers,
Line 6
Incorrect syntax near the keyword 'IF'.
CREATE TRIGGER Trig_Ins_TrackingNumbers ON TrackingNumbers
INSTEAD OF INSERT
AS
BEGIN
IF (SELECT ISNUMERIC(InvoiceNumber) FROM inserted)
IF(EXISTS (SELECT I.InvoiceNumber
FROM Invoices I, inserted Ins
WHERE I.InvoiceNumber=CAST(Ins.InvoiceNumber AS INT)))
INSERT INTO TrackingNumbers(InvoiceNum,InvoiceNumber,
TrackingNumber)
SELECT CAST(InvoiceNumber AS INT), InvoiceNumber, TrackingNumber
FROM inserted
END

[Razvan Socol]

The ISNUMERIC function returns an int (not a boolean value), so the
code may be like this:

[...]
IF (SELECT ISNUMERIC(InvoiceNumber) FROM inserted)<>0
[...]

But this way it doesn't support multi-row updates. Also, for this
issue, I don't think that INSTEAD OF triggers are really needed. To
allow multi-row updates and to simplify the code, I would do something
like this:

CREATE TRIGGER Trig_Ins_TrackingNumbers ON TrackingNumbers
AFTER INSERT
AS
IF EXISTS (SELECT * FROM inserted WHERE ISNUMERIC(InvoiceNumber)=0)
BEGIN
RAISERROR('InvoiceNumber must be numeric !',16,1)
ROLLBACK
RETURN
END

Razvan

[Erland Sommarskog]

Razvan Socol (rs****@gmail.com) writes:

But this way it doesn't support multi-row updates. Also, for this
issue, I don't think that INSTEAD OF triggers are really needed. To
allow multi-row updates and to simplify the code, I would do something
like this:

CREATE TRIGGER Trig_Ins_TrackingNumbers ON TrackingNumbers
AFTER INSERT
AS
IF EXISTS (SELECT * FROM inserted WHERE ISNUMERIC(InvoiceNumber)=0)
BEGIN
RAISERROR('InvoiceNumber must be numeric !',16,1)
ROLLBACK
RETURN
END

One more thing. Isnumeric is a completey useless function. In this case
"signaturefactory" wanted an integer, so the test is better written as:

IF EXISTS (SELECT * FROM inserted
WHERE ltrim(rtrim(InvoiceNumber)) LIKE '%[^0-9)%')
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[signaturefactory]

I appreciate the help. Actually what we are trying to do with the
procedure, is check if the inserted InvoiceNumber exists in the
Invoices table (InvoiceNumber is an Identity column of the Invoices
table) and if so set InvoiceNum of the TrackingNumbers table equal to
the InvoiceNumber. If not we were just trying to ignore the inserted
row without setting any error. The below worked:

CREATE TRIGGER Trig_Ins_TrackingNumbers ON TrackingNumbers
INSTEAD OF INSERT
AS
BEGIN
IF EXISTS(SELECT * FROM inserted WHERE ISNUMERIC(InvoiceNumber)<>0)
IF(EXISTS (SELECT I.InvoiceNumber
FROM Invoices I, inserted Ins
WHERE I.InvoiceNumber=CAST(Ins.InvoiceNumber AS INT)))
INSERT INTO TrackingNumbers(InvoiceNum,InvoiceNumber,
TrackingNumber)
SELECT CAST(InvoiceNumber AS INT), InvoiceNumber, TrackingNumber
FROM inserted
END

This however leads me to another question. In the table above we have
two columns (InvoiceNumber and InvoiceNum) that end up being identical.
InvoiceNumber is of type character and InvoiceNum is of type integer
and has a relationship to an identity column in another table. We
added the extra column because we have a vendor supplied software
package that can only insert text based data so we had to have the
additional column of type text. Does anyone see a way to do this
without having the redundant column?

[Erland Sommarskog]

(si**************@signaturefactory.com) writes:

I appreciate the help. Actually what we are trying to do with the
procedure, is check if the inserted InvoiceNumber exists in the
Invoices table (InvoiceNumber is an Identity column of the Invoices
table) and if so set InvoiceNum of the TrackingNumbers table equal to
the InvoiceNumber. If not we were just trying to ignore the inserted
row without setting any error. The below worked:
No, there are two flaws with this trigger:

1) It does handle multi-row inserts, where some data isnumeric or not.
2) It does not handle the case where the data isnumericm, but it not
convertible to an integer value.

Both these issues were discussed earlier in the thread.

So while it may seem to work, it's an accident waiting to happen.
This however leads me to another question. In the table above we have
two columns (InvoiceNumber and InvoiceNum) that end up being identical.
InvoiceNumber is of type character and InvoiceNum is of type integer
and has a relationship to an identity column in another table. We
added the extra column because we have a vendor supplied software
package that can only insert text based data so we had to have the
additional column of type text. Does anyone see a way to do this
without having the redundant column?

Rather than using an INSTEAD OF trigger, you could have the vendor
package to insert data into a staging table, and from there take it
to the right table. But since I don't know the complete picture, it's
difficult asses the situation properly.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp