Posted by Erland Sommarskog on 03/23/06 00:40

R.A.M. (r_ahimsa_m@poczta.onet.pl) writes:
> I am learning SQL Server 2005.
> I need to create a trigger which increments number of book's
> publications:
>
> CREATE TRIGGER InsertPublication
> ON Publications
> AFTER INSERT
> AS
> BEGIN
> SET NOCOUNT ON;
> DECLARE @Num smallint
> SET @Num = SELECT NumPublications FROM Books WHERE ISBN IN
> (SELECT ISBN FROM inserted);
> UPDATE Books
> SET NumPublications = @Num + 1
> WHERE ISBN IN
> (SELECT ISBN FROM inserted);
> END
>
> Unfortunately I receive a message:
>
> Incorrect syntax near the keyword 'SELECT'.
>
> Could you explain me please how to correct the code?

The syntax error is that when you use a SELECT statement to return
a value in a expression it must be in parentheses:

SET @Num = (SELECT NumPublications FROM Books WHERE ISBN IN
(SELECT ISBN FROM inserted));

You can also write:

SELECT @Num = NumPublications FROM Books WHERE ISBN IN
(SELECT ISBN FROM inserted);

This syntax is older and a bit proprietary. It has the advantage that
you can assign severak variables at the time, but behaviour when no
rows match may take you by surprise. (It leaves the variable unchanged.)

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]