CODEWITHSUNDEEP
oracle-databasetriggers
dhileepan M
dhileepan M

Reputation: 77

Concatenate String with a column in trigger

I have a table called TBL_CAS. In that, FLD_ID as auto increment column and another column is called FLD_CAS_CODE. Now I need to add CAS- as a prefix to FLD_ID and Insert into FLD_CAS_CODE. I need to do this in trigger. I was tried with the below code, But the data in not inserting, What is the problem ?

CREATE OR REPLACE TRIGGER TBL_CAS_TRG
BEFORE  INSERT ON TBL_CAS
FOR EACH ROW
BEGIN
:NEW.FLD_CAS_CODE := TO_CHAR ('CAS')||'-'||:NEW.FLD_ID;
END;

I mean `"cas-"+"fld_id"="cas-fld_id"'

Upvotes: 1

Views: 6332

Answers (3)

dileep
dileep

Reputation: 1

This will also work fine:

CREATE OR REPLACE TRIGGER  TBL_AREA_CODES_TRG 
BEFORE  INSERT ON   TBL_AREA_CODES
FOR EACH ROW
BEGIN
  :NEW.OBRM_AREA_CODE :=  :NEW.STATE_CODE ||'-'||:NEW.DIST_CODE ||'-'||:NEW.CITY_CODE ||'-'||:NEW.AREA_CODE ;
END;

Upvotes: 0

APC
APC

Reputation: 146229

You don't need to put TO_CHAR() around things which are already charcater datatypes. But you should cast the numeric identifier (rather than relying on implicit conversion):

:NEW.FLD_CAS_CODE := 'CAS-'||TRIM(TO_CHAR (:NEW.FLD_ID));

Upvotes: 1

DazzaL
DazzaL

Reputation: 21973

which part isn't working exactly? as your trigger seem to work just fine. 

SQL> create table TBL_CAS( FLD_ID number, FLD_CAS_CODE varchar2(20));

Table created.

SQL> CREATE OR REPLACE TRIGGER TBL_CAS_TRG
  2  BEFORE  INSERT ON TBL_CAS
  3  FOR EACH ROW
  4  BEGIN
  5  :NEW.FLD_CAS_CODE := TO_CHAR ('CAS')||'-'||:NEW.FLD_ID;
  6  END;
  7  /

Trigger created.

SQL> insert into TBL_CAS (fld_id) values (1001);

1 row created.

SQL> select * From TBL_CAS;

    FLD_ID FLD_CAS_CODE
---------- --------------------
      1001 CAS-1001

SQL>

Upvotes: 0

Related Questions