TRIGGER FOR INSERT

[kilabru]

I have a table called LABOR. In this table I have columns named:
LABORCODE, LA21, NAME, ROWSTAMP

I am wanting to create a trigger that will insert into LABORCODE after an insert into LABOR. My example is that I insert a new record so I want LABORCODE to equal 'JOHN'. My real desire is to have it insert a substr such as substr(name, 1, 4)||substr(rowstamp, 1, 4) into LABORCODE as a new record is created.

I have tried everything I know how to do. I get valid triggers as far as ORACLE DBA are concerned and SQLPLUS, but they never work.

Can someone help?

John

 

[kilabru]

This is my code and error

1 create or replace trigger john_labor
2 after insert or update of laborcode on labor
3 for each row
4 begin
5 IF :new.LA21 is null THEN
6 :new.LA21 := :new.laborcode;
7 END IF;
8* End;
SQL> /
create or replace trigger john_labor
*
ERROR at line 1:
ORA-04084: cannot change NEW values for this trigger type

 

[ManjulaMadhu]

Its not clear. Are you trying to say that

you want to insert 2 records in LABOR ?
1. Insert a row where laborcode = JOHN
2. Insert another row where laborcode = substr(name, 1, 4)||substr(rowstamp, 1, 4) ?

can you be more specific .

 

[contactop]

here is the code from what i have understood
create or replace trigger john_labor
before insert on labor
for each row
begin
if inserting then
if :new.laborcode is null then
:new.laborcode:=substrnew.name,1,4)||substrnew.rowstamp,1,4);
end if;
end if;
end;

insert into labor (la21,name,rowstamp) values ('12','contactop','12345678')
it would generate the laborcode what u were asking for

 

[sem]

create or replace trigger john_labor
BEFORE insert or update of laborcode on labor
for each row
begin
:new.laborcode := substrnew.name, 1, 4)
||substrnew.rowstamp, 1, 4);
End;
Regards, Dima

 

[jee]

Just to make it clear - the reason your original trigger failed was that it was an AFTER trigger. Values can only be changed in a BEFORE trigger.

BTW: Dima's solution looks like what you want, if I interpret your requirements correclty.

 

[kilabru]

I figured it out shortly after my post. It has been tested and works great. Please refer to code if you desire. Thanks for eveyone's input.

create or replace trigger john_labor
before insert or update of laborcode on labor
for each row
declare
v_ss2 labor.rowstamp%type;
v_ss labor.la21%type;
begin
SELECT MAXSEQ.NEXTVAL
INTO v_ss2
From dual;
v_ss := substrnew.name, 1, 4)||substr(v_ss2, 1, 5);
IF :new.LA21 is null THEN
:new.LA21 := v_ss;
END IF;
END;