OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » Trigger

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Trigger [message #1230]

Fri, 12 April 2002 09:22

Fred
Messages: 34
Registered: August 1999

Member

Hi all!
I�m trying to create a Trigger (BEFORE INSERT OR UPDATE) that if INSERTING it would let it insert, but if UPDATING it does sth else. The thing is that if UPDATING it DOES NOT HAVE to continue with the insert, it has to make it stop.
Any ideas? Thanx in adv.
Fred.-

Report message to a moderator

Re: Trigger [message #1231 is a reply to message #1230]

Fri, 12 April 2002 09:47

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

here is the sample
-----------------------------
SQL> select * from e;

ENAME
----------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER

14 rows selected.
SQL> get g
1 create or replace trigger sample
2 before insert or update or delete on e
3 for each row
4 begin
5 if inserting then
6 dbms_output.put_line('A new employee '||:new.ename || ' created');
7 end if;
8 if updating then
9 dbms_output.put_line('employee '||:old.ename || 'is updated as '||:new.ename );
10 end if;
11 if deleting then
12 dbms_output.put_line('the employee '||:old.ename || 'is deleted ');
13 end if;
14* end;
SQL> /

Trigger created.

SQL> insert into e values ('viv');
A new employee vivcreated

1 row created.

SQL> delete from e where ename='viv';
the employee vivis deleted

1 row deleted.

SQL> update e set ename='mag';
employee SMITHis updated as mag
employee ALLENis updated as mag
employee WARDis updated as mag
employee JONESis updated as mag
employee MARTINis updated as mag
employee BLAKEis updated as mag
employee CLARKis updated as mag
employee SCOTTis updated as mag
employee KINGis updated as mag
employee TURNERis updated as mag
employee ADAMSis updated as mag
employee JAMESis updated as mag
employee FORDis updated as mag
employee MILLERis updated as mag

14 rows updated.

Report message to a moderator

Re: Trigger [message #2329 is a reply to message #1230]

Sun, 07 July 2002 14:16

Raul Flores
Messages: 1
Registered: July 2002

Junior Member

I have a problem creating a Trigger.
When having an input for an absence by an employee I must to review if this day is marked as busy in the Kardex by another absence avoiding a double input of the same absence, I have if it is busy or not, but I don't know how to stop the inserting, because if it is busy it is an invalid input.
I have the trigger so far:

create or replace trigger TGR_KARDEX
before insert or delete on capturas

declare
-- local variables here
vKarSts_str as varchar(1);
begin
FOR EACH ROW
WHEN (TI.FNTCONCVEAUS_STR(new.CONCEPTO) <> ' ')
BEGIN
IF INSERTING THEN
IF TI.FNTCONCVEAUS_STR(:NEW.Concepto) <> ' ' THEN
IF TI.FNTKARDEXSTS_STR(:NEW.Num_emp,:NEW.Fecha_Pago) <> ' ' THEN
IF TI.FNTKARDEXSTS_STR(:NEW.Num_emp,:NEW.Fecha_Pago) <> '-' THEN
--Kardex Busy Exit and not let to insert this record

ELSE
--Kardex does not exists. Inser the Kardex for this year and update it
INSERT INTO HUMAN.HU_EMPLS_ASISTENCIA
( AS_CIA,
AS_NUM_EMP,
AS_AA_PROC,
AS_MES01,
AS_MES02,
AS_MES03,
AS_MES04,
AS_MES05,
AS_MES06,
AS_MES07,
AS_MES08,
AS_MES09,
AS_MES10,
AS_MES11,
AS_MES12 )
VALUES ( TI.FNTEMPCIA_INT(:NEW.Num_emp),
:NEW.Num_emp,
to_char(:NEW.Fecha_Pago,'y'),
' ',
' ',
' ',
' ',
' ',
' ',
' ',
' ',
' ',
' ',
' ',
' ' ) ;
vKarSts_str := TI.fntKardexdel_str(:NEW.Num_Emp,:NEW.Fecha_Pago,:NEW.Concepto)
END IF
ELSE --Update the record with the absence key
vKarSts_str := TI.fntKardexins_str(:NEW.Num_Emp,:NEW.Fecha_Pago,:NEW.Concepto)
END IF
END IF
ELSE --This is deleting so clear the absence key
vKarSts_str := TI.fntKardexdel_str(:NEW.Num_Emp,:NEW.Fecha_Pago,:NEW.Concepto)
END IF;
END;
end TGR_KARDEX;

Thanks in advance,

Report message to a moderator

Previous Topic:	ENTRY TO THE TABLE ONLY ONCE
Next Topic:	Error 00936: Missing Expression

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 12:42:40 CDT 2024