Trigger to insert Value on a column if?

Question

I need to create a trigger on a table that updates in every insert the value of a column if a condition is present. So in table like this:

enter image description here

I need the trigger to update all null values to '1' if "RegimeIva" = '3' in order to get a result like this:

enter image description here

I have tried:

create or replace TRIGGER AtualizaNat before INSERT ON Cabecdoc  
for each row
BEGIN
  if :new.IntrastatnatA is null and :new.intrastatnatB  is null and :new.regimeiva = '3' then
    :new.intrastatnatA : 1; :new.intrastatnatB : 1;
  else
    :new.intrastatnatA : null; :new.intrastatnatB : null; 
  end if;
END;

This is not working

= is null needs to be is null and := is null; needs to changed to := null; — a_horse_with_no_name
Do you want to update only one row being inserted? If so, what does not work exactly? — Pavel Smirnov
Assigning value should happen by := and not only : so it should be :new.intrastatnatA := 1; instead of :new.intrastatnatA : 1; — hbourchi
Yes, just the one row being inserted. "Msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'or'. — Joao Pontes

Pavel Smirnov Pavel Smirnov · Accepted Answer · 2019-03-28T16:07:11

Corrected few syntax errors in your code.

Here's a little example. It works fine for me:

create table Cabecdoc (IntrastatnatA number, IntrastatnatB number, regimeiva char(1));


create or replace TRIGGER AtualizaNat before INSERT ON Cabecdoc  
for each row
BEGIN
  if :new.IntrastatnatA is null and :new.intrastatnatB  is null and :new.regimeiva = '3' then
    :new.intrastatnatA := 1; :new.intrastatnatB := 1;
  else
    :new.intrastatnatA := null; :new.intrastatnatB := null;
  end if;
END;

--inserting a row...
insert into Cabecdoc values (null, null, '3');

--...and checking
select * from Cabecdoc;


--Output:
IntrastatnatA IntrastatnatB regimeiva
1             1             3

Trigger to insert Value on a column if?

1 Answers