I have a table that when inserting data it needs to shoot a trigger to collect the data of it and another table (both are related by an ID) and insert into another table, when I try to do this the trigger accuses that the table is mutant and gives error. Can anyone give a force?
look at the code of my trigger:
Code:
create or replace trigger insert_tabela_c
after insert on tabela_b
for each row
declare
data date;
peso number(10,4);
precisao varchar(1);
maquina number(6);
produto number(5);
begin
select cod_aca, cod_maquina
into produto,
maquina
from tabela_a
where amo_codigo = :new.amo_codigo;
select ame.ame_data, ame.ame_peso, ame.ame_precisao
into data,
peso,
precisao
from tabela_b ame
where amo_codigo = :new.amo_codigo;
insert into tabela_c(data_med, peso, precisao, cod_maquina, cod_aca)
values(data, peso, precisao, maquina, produto);
end insert_tabela_c;
If the resolution has not been a solutions presented in the links, by Gori, please post as solved, so that others may have as reference in case they require.
create or replace trigger insert_medicoes
after insert on amostragem_medicao
for each row
declare
maquina number(6);
produto number(5);
begin
select cod_aca, cod_maquina
into produto,
maquina
from amostragem
where amo_codigo = :new.amo_codigo;
insert into medicoes2(data_med, peso, precisao, cod_maquina, cod_aca)
values(:new.ame_data, :new.ame_peso, :new.ame_precisao, maquina, produto);
end insert_medicoes;
And there!!!!!
I'm venturing into this oracle world and this is bringing a huge headache because I'm wanting to ride a trigger from a table that feeds a second with only two fields and I can not ... Example:
I have TT and XX tables.
TT Table fields are: Int and DT_Login Date, this when a record is inserted in this precise table q Trigger trigger playing the information on the second XX Q has the following fields: DD Date and TT int;
As I am a first-time sailor I would very much like an aid from the galera ...
Thanks for the Tips Travisolli ... !!!
But I'm breaking my head to a thing I believe is very easy but I'm not really able to walk on it ... Take a look and see if you can help me:
Create or Replace Trigger tr_tt
After Insert or Update on TT
For Each Row
Declare
INT;
DT_Login Date;
BEGIN
Select ref_user, dt_login
into = TT, XX
from TT
WHERE REF_USER = NEW_USER, DT_LOGIN = NEW_LOGIN
CREATE OR REPLACE TRIGGER TR_INSERE_LOG
BEFORE
INSERT OR UPDATE
ON EMP
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
DECLARE
V_SEQ NUMBER(04) := 0;
V_TP_ATU VARCHAR2(01);
BEGIN
IF INSERTING
THEN
V_TP_ATU := 'I';
ELSIF UPDATING
THEN
V_TP_ATU := 'U';
END IF;
SELECT MAX(SEQ)+1 --- AQUI PODERIA SER UMA SEQUENCE TB.
INTO V_SEQ
FROM T_LOG;
INSERT INTO T_LOG
VALUES (V_SEQ,
:NEW.EMPNO,
SYSDATE,
V_TP_ATU);
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001, 'Erro ao popular tabela de log (T_LOG) : ' || SQLERRM);
END;
declare
ref_user varchar(25);
dt_login varchar(12);
begin
select count(*),count(*)
into ref_user,dt_login
from TT;
declare
tt varchar(25);
dd varchar(12);
begin
insert into XX
values (ref_use,:new.tt,dt_logins,:new.dd);
end;
end;
CREATE OR REPLACE TRIGGER TBU_ENCERRAPROGNOSTICO
BEFORE UPDATE OF arquivado1, arquivado2, arquivado3
ON SAPC.PROCESSO
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
vlnPROG NUMBER;
BEGIN
SELECT NVL(COUNT(cod_interno),0)
INTO vlnPROG
FROM SAPC.PROCESSO
WHERE (cod_interno = :NEW.cod_interno)
AND PROB_EXITO In (1,2);
IF(vlnPROG > 0) THEN
IF(:OLD.arquivado1||:OLD.arquivado2||:OLD.arquivado3 <> 'N' AND :NEW.arquivado1||:NEW.arquivado2||:NEW.arquivado3 = 'S') THEN
RAISE_APPLICATION_ERROR(-20003,'Não é permitido o encerramento de processo com prognóstico PROVÁVEL ou POSSÍVEL');
END IF;
END IF;
END;