Updating multiple records after insert with a trigger

Question

3.00/5 (2 votes)

See more:

Hi
Well basically I need this trigger to work after a user inserts multiple records into the database.

SQL

CREATE TRIGGER IntelligentThinkingDev
   ON AuditResults AFTER INSERT
AS 
BEGIN
	DECLARE @OPTIONID INT
	DECLARE @ISoptional INT
	DECLARE @AuditResul INT
	SELECT @OPTIONID =  OPTIONID FROM INSERTED
	SELECT @ISoptional =  ISoptional FROM INSERTED
	SET @AuditResult = CASE WHEN @optionID = 0 and @ISoptional = 1 THEN
	UPDATE AuditResults SET optionid = null WHERE ResultID = (SELECT ResultID FROM INSERTED)	
END

I am getting just the incorrect syntax near 'UPDATE'

Thank you in advance

Posted 19-Dec-13 3:07am

Member 9374423

Updated 19-Dec-13 21:28pm

v2

Add a Solution

Comments

Maciej Los 19-Dec-13 11:57am

Not clear! Please, be more specific and provide more details...

2 solutions

Solution 1

No trigger works on "multiple records". Each time a record is inserted, the trigger will be run. If you insert 10 records, the trigger will run 10 times - once for each inserted record as it is being inserted.

Posted 19-Dec-13 10:09am

dbrenth

Comments

[no name] 19-Dec-13 17:57pm

Short and clear and how it is

Member 9374423 20-Dec-13 1:50am

Thank you, Now I know

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

creepz03 · Accepted Answer · 2013-12-19T13:38:00

Solution 2

Try this one.. if you are trying to update the optionid of the currently inserted data.

SQL

CREATE TRIGGER ThisDatabase
   ON OtherTable AFTER INSERT
AS 
BEGIN
	DECLARE @OPTIONID INT
	DECLARE @ISoptional INT
	SET @OPTIONID = SELECT OPTIONID FROM INSERTED
	SET @ISoptional = SELECT ISoptional FROM INSERTED
	CASE WHEN @optionID = 0 and @ISoptional = 1 THEN
	BEGIN
		UPDATE OtherTable SET optionid = null WHERE YourPrimaryKey = (SELECT YourPrimaryKey FROM INSERTED)
	END
END

Posted 19-Dec-13 13:38pm

creepz03

Comments

Member 9374423 20-Dec-13 0:21am

Incorrect syntax near the selects and the case?

Member 9374423 20-Dec-13 3:20am

this resolved the issue with the selects

CREATE TRIGGER IntelligentThinkingDev
ON AuditResults AFTER INSERT
AS

BEGIN
DECLARE @OPTIONID INT
DECLARE @ISoptional INT
SELECT @OPTIONID = OPTIONID FROM INSERTED
SELECT @ISoptional = ISoptional FROM INSERTED

CASE WHEN @optionID = 0 and @ISoptional = 1 THEN
BEGIN
UPDATE AuditResults SET optionid = null WHERE ResultID = (SELECT ResultID FROM INSERTED)
END
END

but I still have an issue with the case error