how to create this trigger in SQL?

Question

I have the following table orders(Student_ID, Order_ID, order_date, completed, payment, card_no, total_price). I want to create trigger that forbids inserting values in order if payment is card, but card_info is missing.

create or replace trigger check_card_info
before insert on orders
for each row
begin
if (:new.Payment='card' and :new.card_No=null) then
raise_application_error(-20002,'card info is missing');
end if;
end;
.

I have tried this. It creates the trigger without errors, but the logic does not work. By logic it should forbid inserting the following values, but it does not.

insert into orders values (21200122, 11,'01-FEB-2023','no', 'card', null, 150);

`:new.card_No=null` should be `:new.card_No IS null`, indeed using a check constraint ,as the answer suggests, rather than a trigger is a better option. — Barbaros Özhan, Apr 15 '23 at 05:39
[Null Conditions](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Null-Conditions.html#GUID-657F2BA6-5687-4A00-8C2F-57515FD2DAEB):A NULL condition tests for nulls. *This is the only condition that you should use to test for nulls* — astentx, Apr 15 '23 at 07:45
Not particularly important, but PL/SQL syntax doesn't require brackets around `if` conditions as they are already terminated by the `then` keyword. (Also, the code is written in PL/SQL and not SQL.) — William Robertson, Apr 15 '23 at 09:30
As per the linked duplicate, use `IS NULL` instead of `= NULL`. — MT0, Apr 15 '23 at 09:54

score 1 · Answer 1 · edited Apr 15 '23 at 05:36

1

Why not add a constraint to the table?

ALTER TABLE orders
  ADD CONSTRAINT chk_payment_card_no_not_null   
CHECK ( ( payment  = 'card' AND card_No IS NOT NULL)
       OR payment != 'card' );

edited Apr 15 '23 at 05:36

Barbaros Özhan

59,113
10
31
55

answered Apr 15 '23 at 05:08

Al_M42KM

13
1
3

It works, thank you! But the reason I am using trigger is so I can display specific message on raise_application_error. Later I need to catch this error in JDBC and display the message. – Bexultan Shalabayev Apr 15 '23 at 07:11

score 1 · Answer 2 · answered Apr 15 '23 at 09:21

If it has to be a trigger, code you wrote was close; it is just that nothing is equal to null. You can't check whether card_no = null, but whether card_no is null.

As of rest of your code, several objections:

in trigger, use when clause instead of if-then-else
when performing insert, always specify column names you're inserting data into. The way you did it, it is impossible to know which value represents what
moreover, it seems that you're inserting date value. If that's so, then don't insert a string (that's what '01-FEB-2023' is) and don't rely on implicit datatype conversion. If NLS settings change, you'll get an error (which one? It depends on those settings). Insert proper date datatype value instead, either as date literal (which is what I did in example that follows), or use to_date function with appropriate format model

As you didn't provide table description, I'll guess.

SQL> create table orders
  2  (order_id   number,
  3   col2       number,
  4   order_date date,
  5   status     varchar2(5),
  6   payment    varchar2(10),
  7   card_no    varchar2(10),
  8   col7       number);

Table created.

Trigger:

SQL> create or replace trigger check_card_info
  2    before insert on orders
  3    for each row
  4    when (new.payment = 'card' and new.card_no is null)
  5  begin
  6    raise_application_error(-20002, 'card info is missing');
  7  end;
  8  /

Trigger created.

Testing: invalid row first (card_no is missing):

SQL> insert into orders (order_id, col2, order_date, status, payment, card_no, col7)
  2    values (21200122, 11, date '2023-02-01', 'no', 'card', null, 150);
insert into orders (order_id, col2, order_date, status, payment, card_no, col7)
            *
ERROR at line 1:
ORA-20002: card info is missing
ORA-06512: at "SCOTT.CHECK_CARD_INFO", line 2
ORA-04088: error during execution of trigger 'SCOTT.CHECK_CARD_INFO'

Valid row:

SQL> insert into orders (order_id, col2, order_date, status, payment, card_no, col7)
  2    values (21200122, 11, date '2023-02-01', 'no', 'card', 'Card 123', 150);

1 row created.

SQL>

how to create this trigger in SQL?

2 Answers2