Help with trigger insert

Question

Help with trigger insert

Navigation

#1 by (0 votes)

0

I'm new to SQL and I want to make a page as a new user record, and that this information is stored in a table, (this part I already have) The problem is that when creating a new user, I have 2 tables, one of companies and another of users, then .. when clicking on the user save button or create user, the same user ID of the User table must be the same user ID of the company table. The trigger that I currently have creates 2 rows, one with the information I need in the columns that I need except the ID, the other line of record leaves all the columns null, and the ID registers it.

How can I make it so that the same ID is registered in a single line?

this is the trigger that I currently have:

create or replace trigger compania_trigger
   before insert or update on usuarios
   for each row



   begin
      if inserting then
         if :new.compania_id is null then
           select to_number(sys_guid(),'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX')
           into :new.compania_id
           from dual;
         end if;
         :new.created    := sysdate;

      end if;

      if inserting or updating then
         :new.updated    := sysdate;

      end if;

   insert into empresas(compania_id) values (:new.compania_id);

end;

sql oracle trigger

asked by zhio19 18.12.2018 в 18:29

source

1 answer

Execute query and assign values to excel columns Creation of XLS from Angular TypeScript [closed]

score 0 · Answer 1

What if you define a variable inside the trigger? similar to this code:

CREATE OR REPLACE TRIGGER orders_before_update
BEFORE UPDATE
   ON orders
   FOR EACH ROW

DECLARE
   v_username varchar2(10);

BEGIN

   -- Find username of person performing UPDATE on the table
   SELECT user INTO v_username
   FROM dual;

   -- Update updated_date field to current system date
   :new.updated_date := sysdate;

   -- Update updated_by field to the username of the person performing the UPDATE
   :new.updated_by := v_username;

END;

And instead of doing

SELECT user INTO v_username

you have to do:

select sys_guid() INTO  from dual

and your variable you declare it like this:

DECLARE
uid raw(16)