Triggers

[binjovi]

Hello Guys

Hope this wont fustrate you all, I quite funny I require some operations to be completed using the database triggers. The problem I am facing is when I create a trigger on a table and started the file the trigger got created. When I inserted some records to the table where trigger is created it inserted the value and when I say rollback it rollbacked the data but some other times the operation is not rolling back the data..

In short after inserting a value to a table where trigger is created sometimes it allows rollback and sometimes not.

Thanks for all help..

Thanks in advance
bins.........

 

[carp]

This is rather difficult to analyze without seeing your code.
Also, I'm not sure what you mean by "starting the file". Also, how are you inserting the data? If you are doing this via SQL*Plus, is it possible you have autocommit on?

 

[binjovi]

Hi carp thanks for the response...

I have created a before insert trigger which updates the same table with salary added to 1000 more to the existing salary specification...

The trigger is working perfect and as per the trigger i got the output in the table but when I issued a ROLLBACK it didnt rollback and the inserted data is available in the table but after some time I inserted a record it did as per the trigger and again when i issued the ROLLBACK it rollbacked this data which i inserted.........

Thanks
bins

 

[Guest_imported]

hi guys
can any body help me out in creating primary key
using triger...
i ha dcreated a sequence and then used in trigger
but it is giving an erro


create sequence c_seq
increment by 1
start with 1;


create or replace trigger no_generation
before insert on client_master
for each row
declare
pk_val varchar2(5);
begin
select lpad(to_char(c_seq.nextval),5'0')
into pk_val from dual;
:new.client_no:='c'||pk_val;
end;

error comes while inserting
SQL> insert into client_master(name) values('prasad');
insert into client_master(name) values('prasad')
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "SCOTT.NO_GENERATION", line 6
ORA-04088: error during execution of trigger 'SCOTT.NO_GENERATION'


please tell me how to come out of this

 

[rcurva]

If your client_no is a char(5)/varchar2(5)/number then you actually have a problem with value error.

 

[sem]

select lpad(to_char(c_seq.nextval),5'0')
Add comma before '0'

 

[rosw]

try ltrim(to_char(c_seq.nextval,'00009')) - you'll need the ltrim because to_char may left pad the value with a space.

 

[kindus]

Hi Binjovi,
I just read this posting today as it came up due to some unrelated posting though. I think u must have resolved your issue, but since it is not posted here I am putting what I think might be happening in your trigger. There is an option of defining trigger an autonomous transation with PRAGMA statement. If you define your trigger as an autonomous transaction and commit within the trigger, the changes will not be rollback even if the main trasaction which has triggered this transaction is rollback. If you do not need this, you may remove the PRAGMA statement. This is feature which is very usefull in many scenerios.

Others, please use new topic for different subject.

 

[binjovi]

Thanks Kindus
The problem it still appears . can you tell me more on it aor can you tell me the a location where i can go and get more information about this topic