SQL: trigger on insert

Question

I have a table X with a column enabled which has default value as 'T'. I need a trigger which can put value into the enabled column based on value present in another table Y which has a row called trusted with value 'Y'.

So everytime table X is inserted with values i want that the value of enabled should be updated based on value of table Y's row trusted.

If trusted = 'N' then set enable ='F' Else enabled = 'T'

Sorry for not providing any code. I am a bit confused about the strategy as of now.

Table X:

id  username enable
-------------------
1   ab          T
2   cd          T
3   ef          T
4   gh          T

Table Y:

key     value
-------------
trusted    Y
tested     N

You should provide some example input data in your tables along with the expected output. That way, we can help you better. We don't need to see your exact data, you could make something up that represents your situation. — Boneist
– Boneist, Commented Dec 16, 2016 at 10:13
Thanks for adding the extra information; it makes it a lot easier to work out what you're trying to do. *{:-) — Boneist
– Boneist, Commented Dec 16, 2016 at 11:56

Ponder Stibbons · Accepted Answer · 2016-12-16 11:52:13Z

create or replace trigger tg_ins_x before insert on x for each row
begin
  select case when value = 'Y' then 'T' else 'F' end 
    into :new.enabled 
    from y where key = 'trusted';
end;

If You don't want to override provided value for enabled:

create or replace trigger tg_ins_x before insert on x for each row
begin
  if :new.enabled is null then
    select case when value = 'Y' then 'T' else 'F' end 
      into :new.enabled 
      from y where key = 'trusted';
  end if;
end;

You can add exception (too many rows, no data found, others) raising error or assigning default value:

exception when no_data_found then
    :new_enabled := 'F';

Boneist · Accepted Answer · 2016-12-16 11:52:28Z

Rather than using a trigger, why not just have a view instead?

Something like:

create or replace view x_vw (id, username, enable) as
select x.id
       x.username
       case when (select upper(value) from y where key = 'trusted') = 'N' then 'F'
            else 'T'
       end enable
from   x;

N.B. This assumes that if you change the trusted row's value to N, you want to change all the rows in X to 'F'. If you don't want that to happen, then stick with a trigger.

Collectives™ on Stack Overflow

SQL: trigger on insert

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related