Creating an If-else trigger to input a value into another table based on an input in the original table

Question

I have two tables. One is called: TFiled_for_Office with columns First Name, Last Name, Office, Party, Signatures. The other is:TMustPayFee with columns First Name, Last Name, Signatures, FeeStatus. I'm trying to create a trigger where, when inputting values in TFiled_for_Office, if the signatures entered are less than 500, the FeeStatus field will return a text value saying they must pay a filing fee. If it's more than 500, the text says they can bypass fee.

The error I'm getting is: An expression of non-boolean type specified in a context where a condition is expected, near 'then'. Maybe I'm adding extraneous syntax that shouldn't be there. Here is my query, let me know what the issue is. Thank you!

CREATE TRIGGER Filing_Fee_Eligibility
   ON  TFiled_for_Office
   AFTER INSERT
AS 
BEGIN
declare @signatures int;
declare @First_Name varchar(20);
declare @Last_Name varchar(20);

SET NOCOUNT ON;
-- Insert statements for trigger here
SELECT @signatures = Signatures from inserted
SELECT @First_Name = @First_Name from inserted
SELECT @Last_Name = @Last_Name from inserted



if (select @signatures from TFiled_for_office where @signatures < '500')

then insert into TMustPayFee (First_Name, Last_Name, Signatures, FeeStatus) 
values (@First_Name, @Last_Name, @signatures, 'Candidate must pay filing fee of $2000')
else insert into TMustPayFee (First_Name, Last_Name, Signatures, FeeStatus)
values (@First_Name, @Last_Name, @signatures, 'Candidate eligible to bypass filing fee')
END
GO

Answer 1

In your IF expression, you are selecting rows. That statement MUST return a single row to be evaluated correctly when compared to the string literal. That assumption is false. But that IF statement and the preceding assignments are just problematic and not needed once you address the more fundamental problem

Your code suffers from a common flaw. You assume a trigger executes on a per-row basis. The virtual inserted and deleted tables can contain any number of rows - you must write your code to work correctly to handle any number of inserted rows. A starting point is:

CREATE TRIGGER Filing_Fee_Eligibility
   ON  dbo.TFiled_for_Office
   AFTER INSERT
AS 
SET NOCOUNT ON;

if exists (select * from inserted where Signatures < 500)
   insert into TMustPayFee (First_Name, Last_Name, Signatures, FeeStatus)
   select ... from inserted where Signatures < 500;

if exists (select * from inserted where Signatures >= 500)
    insert into TMustPayFee (First_Name, Last_Name, Signatures, FeeStatus)
    select ... from inserted where Signatures >= 500;
go

You might wonder about the IF statements. Even if a DML statement affects no rows (i.e., if the SELECT statement returns no rows), any trigger associated with that type of DML statement will execute - and the virtual trigger tables will be empty. So the IF statement avoids that. Usually that is a trivial optimization but it is better to avoid work that is not needed.

Notice the good habits to develop. Terminate statements. Schema-qualify table names. Avoid implicit conversion. You wrote @signatures < '500' - why? The variable is INT and you compare to a string literal.

Also think about how you define your requirements. You wrote two cases - when signatures is less than 500 and when signatures is greater than 500. What about when signatures is exactly 500. I corrected for that based on how your original code was written.

Answer 2

TSQL if statement is like

if (condition1) begin
...
end
else if (condition2) begin
...
end
else begin
...
end

no then