OraFAQ Forum: SQL & PL/SQL » problem related to trigger

Home » SQL & PL/SQL » SQL & PL/SQL » problem related to trigger

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

Thu, 21 June 2012 03:33

neetesh87
Messages: 280
Registered: September 2011
Location: bhopal

Senior Member

hello experts,
i want to know the logic behind 'of' clause in trigger when we need to create a trigger before dml effect on particular column
of our table .
if we include all three dml operation then we know about 'delete', it delete a row not a particular column , by 'delete statement'.
so it is right with delete. and no problem with update also, but in 'insert statement' we can put null in non pk column or may be we did not include these columns in our 'insert statement' , then null will be inserted.
now we create a trigger with 'of' clause , script is like.......

create or replace trigger trg_check_sal before insert or update of sal on emp for each row
begin
if inserting then
raise_application_error(-20101,'can not perform insertion');
elsif updating then
raise_application_error(-20101,'can not perform updation');
end if;
end;
/

and i perform these statements---

insert into emp(empno,sal)values(121,1000);

then oracle gives an error--

ORA-20101: can not perform insertion
ORA-06512: at "SCOTT.TRG_CHECK_SAL", line 3
ORA-04088: error during execution of trigger 'SCOTT.TRG_CHECK_SAL'

i know this is because of trigger, but

when i fired another statement..

insert into emp(empno)values(121);

then oracle also giver error, because of trigger.

ORA-20101: can not perform insertion
ORA-06512: at "SCOTT.TRG_CHECK_SAL", line 3
ORA-04088: error during execution of trigger 'SCOTT.TRG_CHECK_SAL'

i could not understand that i am not inserting values in 'sal' column, then why trigger is fired. please explain me.

thanx in advance.....

Report message to a moderator

Thu, 21 June 2012 03:52

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Your trigger is fired whenever you:

Insert a new record
Update the SAL column

This is logical: you can update a single column, but you cannot insert a single column without inserting the other columns as well.

MHE

[Updated on: Thu, 21 June 2012 03:52]

Report message to a moderator

Thu, 21 June 2012 03:57

neetesh87
Messages: 280
Registered: September 2011
Location: bhopal

Senior Member

thanks for your kind response,
but we can insert here 'null' in 'sal' column through this statement --

insert into emp(empno)values(121);

here we are not inserting valu in 'sal' column.

thanks again.......

Report message to a moderator

Thu, 21 June 2012 04:04

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Yes you are. You didn't provide a value explicitly to Oracle, so it either takes the column's default value or NULL. But it's there. Oracle has no "before insert of <column>" clause when you create a trigger. Like I said, it is a row wise operation: you insert a row or you don't.

You should evaluate the column. Check whether it is not null. If it is, that means that the user has explicitly provided a value.

MHE

Report message to a moderator

Thu, 21 June 2012 05:11

neetesh87
Messages: 280
Registered: September 2011
Location: bhopal

Senior Member

NO, its (sal column) is not a 'not null' column.it can accept null.
means we can say that 'of' clause in trigger is useful only for 'update' statement.

thanx again.....

Report message to a moderator

Thu, 21 June 2012 06:06

Maaher
Messages: 7065
Registered: December 2001

Senior Member

I meant to say: check in the triggercode whether the value is not null.
Something like this:

create or replace trigger trg_check_sal before 
     insert 
   or 
     update of sal 
   on emp 
   for each row
begin
  if inserting then
    if :NEW.sal Is Not Null Then -- Provided an explicit value for SAL
      _do_something_
    end if;
  elsif updating then
    raise_application_error(-20101,'can not perform updation');
  end if;
end;
/

As for your last remark "means we can say that 'of' clause in trigger is useful only for 'update' statement.": that is correct.

MHE

Report message to a moderator

Previous Topic:	how to identify which user has changed table structure
Next Topic:	oracle dbms job help

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon May 05 02:26:31 CDT 2025