CODEWITHSUNDEEP
sqlif-statementtriggers
Slwvin_45
Slwvin_45

Reputation: 37

if else in trigger in sql

I m trying to create after insert trigger with if else statement.I can do it without if else, but get error with it. So, in table 'booking' there is a column customer_id. There is a table customer, where i have a column 'type'. Depending on the value in this column i want add a row to booking_status table, but i get error in syntax. I m just learning sql, please, help if you can.

AFTER INSERT ON booking
FOR EACH ROW 
IF (SELECT type FROM customer WHERE ID=customer_id) = 'not trust'
BEGIN
INSERT INTO booking_status (id, status_name, booking_id) VALUES(NEW.booking_number, 'Checking', NEW.booking_number)
END
ELSE
INSERT INTO booking_status (id, status_name, booking_id) VALUES(NEW.booking_number, 'Approved', NEW.booking_number);

Upvotes: 1

Views: 33242

Answers (1)

Barmar
Barmar

Reputation: 780851

Your IF syntax is incorrect. You need THEN before the statements to be executed when the condition is true, you need END IF; at the end of it, and you need ; at the end of each statement. You don't need BEGIN/END when there's just a single statement in the THEN clause.

IF (SELECT type FROM customer WHERE ID=customer_id) = 'not trust'
THEN 
    INSERT INTO booking_status (id, status_name, booking_id) VALUES(NEW.booking_number, 'Checking', NEW.booking_number);
ELSE
    INSERT INTO booking_status (id, status_name, booking_id) VALUES(NEW.booking_number, 'Approved', NEW.booking_number);
ENDIF;

But don't need an IF statement, you can use the IF function in a single INSERT:

INSERT INTO booking_status(id, status_name, booking_id)
VALUES (NEW.booking_number, 
        IF((SELECT type FROM customer WHERE ID=customer_id) = 'not trust', 'Checking', 'Approved'),
        NEW.booking_number);

Upvotes: 8

Related Questions