trigger and updates

From: cptkirkh <khill_at_scic.com>
Date: 29 Jan 2007 09:35:55 -0800
Message-ID: <1170092155.369471.50510@j27g2000cwj.googlegroups.com>

I want to fill a table from a triggered event on another table. The field that will be changed in the destination table will be determined by what is changed on the source. I think I have set this up right but I get an ORA-1733 error on the word "field" in the update statement. It says virtual column not allowed here. How can I resolve this or what does anybody suggest is a better way to work aorund this problem. Or of course is my PL/SQL wrong? Thnaks for your help. Here is the PL.SQL

FIELD VARCHAR2(20);

x number;
y number;
old date;
new date;

id varchar2(10);

BEGIN
  FIELD := CASE

                 WHEN x = 1 AND y = 1 THEN 'CLI_CIC_NPDATE'

				 ELSE 'NOT SURE'
				  END;

  DBMS_OUTPUT.PUT_LINE(FIELD); if nvl(old,'01-JAN-1901') <> nvl(new,'01-JAN-1901') and field is not null then

           UPDATE DESIGNATION_INFO_TEST SET FIELD = sysdate WHERE CLI_RID = id;

		   ELSE
		   		INSERT INTO DESIGNATION_INFO_TEST(CLI_RID,FIELD)
         VALUES (id,sysdate);
      end if;

end; Received on Mon Jan 29 2007 - 11:35:55 CST