Multiple conditions in one trigger

Question

Multiple conditions in one trigger

gautham.gn

Mr or Mrs. 500

Points: 590
More actions
February 10, 2014 at 6:55 am

#282263

Hi,
I have a requirement that two update conditions may happen for a table A. And we need to store the updated records in table B in such a way that
When Condition A is true it should insert into a,b,c Columns
and if condition B is true then it should insert into a,c,d columns into Table B.
How could a trigger syntax will be in this case??
Thanks in Advance,,
Gautham.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

gautham.gn (2/10/2014)
Hi,
I have a requirement that two update conditions may happen for a table A. And we need to store the updated records in table B in such a way that
When Condition A is true it should insert into a,b,c Columns
and if condition B is true then it should insert into a,c,d columns into Table B.
How could a trigger syntax will be in this case??
Thanks in Advance,,
Gautham.

it depends ont he specific details;

if the conditions are mutualy exclusive, it would be two insert statements, both of which could potentially fire on multi row inserts.

if one condition is a subset of the other, then you need some more logic.

INSERT INTO TableB(a,b,c)

SELECT a,b,c FROM INSERTED

WHERE ConditionA=1

INSERT INTO TableB(a,c,d)

SELECT a,c,d FROM INSERTED

WHERE ConditionB=1

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 2

Lowell (2/10/2014)
INSERT INTO TableB(a,b,c)
SELECT a,b,c FROM INSERTED
WHERE ConditionA=1
INSERT INTO TableB(a,c,d)
SELECT a,c,d FROM INSERTED
WHERE ConditionB=1
[/code]

Be careful. What if the first INSERT extends the ConditionB set?!

Igor Micev,My blog: www.igormicev.com

Lowell SSC Guru Points: 323518 More actions · Answer 3

Igor Micev (2/10/2014)
Lowell (2/10/2014)
INSERT INTO TableB(a,b,c)
SELECT a,b,c FROM INSERTED
WHERE ConditionA=1
INSERT INTO TableB(a,c,d)
SELECT a,c,d FROM INSERTED
WHERE ConditionB=1
[/code]
Be careful. What if the first INSERT extends the ConditionB set?!

exactly why i said it depends on whether the conditions are mutually exclusive or not.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 4

Lowell (2/10/2014)
Igor Micev (2/10/2014)
Lowell (2/10/2014)
INSERT INTO TableB(a,b,c)
SELECT a,b,c FROM INSERTED
WHERE ConditionA=1
INSERT INTO TableB(a,c,d)
SELECT a,c,d FROM INSERTED
WHERE ConditionB=1
[/code]
Be careful. What if the first INSERT extends the ConditionB set?!
exactly why i said it depends on whether the conditions are mutually exclusive or not.

Oh yes, I agree completely with you.

Igor Micev,My blog: www.igormicev.com