Trigger problem

From: Klaus Sonnenleiter <100063.3631_at_CompuServe.COM>
Date: 1996/06/19
Message-ID: <4q7nfk$93p$1_at_mhafn.production.compuserve.com>#1/1

I'm using the trial version of Oracle WGS 7.1 for Unixware and I cameacross a problem using triggers: I'm trying to implement a check beforeinsert to make sure a text field does not exceed the maximum length of2000 characters. I keep getting an error message saying "string literaltoo long" so I assume the code I'm using doesn't work.

Here's the code I'm using
SQL> create or replace trigger msgtext_cut   2 before insert on system.f_msgtext
  3 for each row
  4 declare
  5 tmp_str varchar2(32767) := null;
  6 i number := null;
  7 j number := null;
  8 begin
  9 tmp_str := :new.txtblock;
 10 j := TRUNC(LENGTH(tmp_str)/2000)+1;  11 select mtext_seq.nextval into i from dual;

 12    :new.id := i;
 13    :new.ord_id := j;
 14    :new.txtblock := substr (tmp_str, (j-1)*2000+1);
 15    if j > 1 then
 16      insert into system.f_msgtext (msgid, txtblock) values 

(:new.msgid, substr (tmp_str, 1, (j-1)*2000)); Received on Wed Jun 19 1996 - 00:00:00 CEST