How to Insert only if value is true

Patrick Rainer

5y1.2kSQL

Dear all

I am trying to insert only if a specific related value is true. I tried the code below, but was not working so far. How can I achieve this in a trigger?

CREATE TRIGGER CreateTest
ON Participations
AFTER INSERT
AS
begin
set nocount on;
DECLARE @testNeeded bit;
insert into Tests(participationid)
values
((SELECT i.ParticipationId
from inserted as i
inner join Executions on i.ExecutionId = i.ExecutionId
inner join Courses C on C.CourseId = Executions.CourseId
where c.TestNeeded = 1))
end
go

Patrick

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sachin Singh5y ago
what is your full business requirement
you have tables as course, Execution and Participation , in which table you are inserting data and want trigger to do its work??
Patrick Rainer5y ago
@
Sachin Singh

You are right, but how can I tell sql to not to insert if the value is null?

I mean in pseudo code:

If value = true then
Insert row
else if value = false
return
end if
Sachin Singh5y ago
Inserted table only contains the copy of values which are inserted in the table , in your case you perhaps not inserting ParticipationId as it is auto incremented or similar, so it is null , now when you are trying to insert null value into not nullable column causing the error.
Patrick Rainer5y ago
I use ms sql, so I tried:
SELECT i.ParticipationId
          from inserted i
                   inner join Executions e on i.ExecutionId = e.ExecutionId
                   inner join Courses c on c.CourseId = e.CourseId
          where c.TestNeeded = 1
            AND c.TestNeeded is not null
But getting the error:
Cannot insert the value NULL into column 'ParticipationId', table 'prdevstu_TrainingTracking02.patrick.rainer.Tests'; column does not allow nulls. INSERT fails.
Patrick Rainer5y ago
i dont know much about database but i noticed(your using same alias) i.ExecutionId = i.ExecutionId so replaced left side i with Executions

inner join Executions on Executions.ExecutionId = i.ExecutionId

Correct would be:
inner join Executions on Executions.ExecutionId = i.ExecutionId
Right sorry, but that still does not work as expected because it tries to insert null, which should not happen in this case I would like to not insert anything ...
Khaja Moizuddin5y ago
1. SELECT i.ParticipationId
2. from inserted i
3. inner join Executions e on i.ExecutionId = e.ExecutionId
4. inner join Courses c on c.CourseId = e.CourseId
5. where c.TestNeeded = 1 && i.ParticipationId <> NULL

Rajanikant Hawaldar5y ago

i dont know much about database but i noticed(your using same alias) i.ExecutionId = i.ExecutionId so replaced left side i with Executions

inner join Executions on Executions.ExecutionId = i.ExecutionId