Hello,
I am learning SQL Server 2005.
I need to create a trigger which increments number of book's
publications:
CREATE TRIGGER InsertPublicati on
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/ 2 124936
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 InsertPublicati on 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 InsertPublicati on
ON Publications
AFTER INSERT
AS
BEGIN
UPDATE Books
SET NumPublications = NumPublications + 1
FROM Books, inserted
WHERE Books.ISBN = inserted.ISBN
END
R.A.M. (r_********@poc zta.onet.pl) writes: I am learning SQL Server 2005. I need to create a trigger which increments number of book's publications:
CREATE TRIGGER InsertPublicati on 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****@sommarsk og.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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: DTB |
last post by:
I am having trouble creating an INSTEAD OF trigger in SQL Server to
replicate a BEFORE UPDATE trigger from ORACLE.
Here is a sample of the ORACLE BEFORE UPDATE trigger:
CREATE TRIGGER myTRIGGER ON MYTABLE
begin
:new.DT := SYSDATE;
if :new.NM is NULL then
:new.NM := USER;
end if;
end myTRIGGER;
|
by: Elvira Zeinalova |
last post by:
Hei,
We have 2 MS SQL SERVER 2000 installed on 2 different servers (2 separated
machines).
I am triing to connect them så that when one row is added to the table in
the database in main server - then the same row is added to the same table
in the second server database.
I made the insert trigger on the table in the first server ( the second
server is added as a linked server):...
|
by: Trevor Best |
last post by:
I don't know if this has been reported before but it appears to be a
bug with Access.
If I create two tables both with an identity column then create an
insert trigger on table1 that inserts a related record into table2, now
create a form on table1 with a subform on table2. Insert records into
the main form to your heart's content and everything's fine, each main
record automatically gets a child record and so far the identity
columns...
|
by: fumanchu |
last post by:
I've got to let end users (really just one person) load billing batch
files into a third party app table. They need to specify the billing
cycle name, the batch name, and the input file name and then I can use
these values to execute a SQL Server stored procedure to load them
into the batch table from the input file (the stored procedure uses
Dynamic SQL to Bulk Insert the file to a temp table then processes it
into the final table.)
My...
|
by: dba_222 |
last post by:
Dear Experts,
I'm an Oracle guy, who is being given more SQL Server assignments
lately.
I've been looking for things on the web about this, but I can't
anything so far.
In Oracle, I you can create a trigger on a table that -only- fires if
| |
by: Jobs |
last post by:
Download the JAVA , .NET and SQL Server interview with answers
Download the JAVA , .NET and SQL Server interview sheet and rate
yourself. This will help you judge yourself are you really worth of
attending interviews. If you own a company best way to judge if the
candidate is worth of it.
http://www.questpond.com/InterviewRatingSheet.zip
|
by: Zeljko Bilandzija |
last post by:
Hello!
I have a problem, and I looking for help if someone can handle this.
I use asp.net 2.0, and I create web site which support users from internet
(Web Site Administration Tool) and with that option i get database named
ASPNETDB.MDF.
When someone via page is creating account, database creates a new record in
aspnet_users table.
I made another table (table User_Data, columns UserID, Level, Points, ...),
and i want that these data...
|
by: Tony Toews [MVP] |
last post by:
Thanks to a posting by fellow MVP Steve Foster
On a computer that is running Windows Vista, Windows Server 2008, or
Windows XP, an incorrect value is returned when an application queries
the identity column value of a newly inserted row in various versions
of SQL Server 2005 and of SQL Server 2000
http://support.microsoft.com/kb/951937
Now I don't *KNOW* that this affects Access but the KB article states
"Applications that use the...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
| |
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |