468,251 Members | 1,372 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,251 developers. It's quick & easy.

SQL Server 2005: TRIGGER AFTER INSERT

Hello,
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?
I am new to SQL Server.
Thank you very much.
/RAM/
Mar 22 '06 #1
2 124625

R.A.M. wrote:
Hello,
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?
I am new to SQL Server.
Thank you very much.
/RAM/

CREATE TRIGGER InsertPublication
ON Publications
AFTER INSERT
AS
BEGIN
UPDATE Books
SET NumPublications = NumPublications + 1
FROM Books, inserted
WHERE Books.ISBN = inserted.ISBN
END

Mar 22 '06 #2
R.A.M. (r_********@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Mar 22 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Elvira Zeinalova | last post: by
1 post views Thread by Zeljko Bilandzija | last post: by
8 posts views Thread by Tony Toews [MVP] | last post: by
reply views Thread by NPC403 | last post: by
reply views Thread by kermitthefrogpy | last post: by
reply views Thread by zattat | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.