Setting up trigger problem

From: Aaron Rouse <aaron_rouse_at_yahoo.com>
Date: 12 Jun 2002 13:27:53 -0700
Message-ID: <a57b6daf.0206121227.354f8911@posting.google.com>

I am trying to figure out when I execute the below the trigger does not get created. This is a script that I ran on my last project and do not recall having any problems. When I try to run this in Toad it pops up with a "Variables" window and wanting me to define a Value for the Variable named ":NEW" and when I run it in SQL Plus I get no error, but no trigger is made:

create table PICKLISTS (

	ID NUMBER(8) NOT NULL,
	TYPE VARCHAR2(100) NOT NULL,
	VALUE VARCHAR2(250) NOT NULL,
	LOCID NUMBER(8) DEFAULT 0 NOT NULL, 
	SORTORDER NUMBER(8) NULL,
	SITENAME VARCHAR2(50) NULL,
	PRIMARY KEY (ID)

);

create sequence SEQ_PICKLISTS

	INCREMENT BY 1
	START WITH 1
	NOMAXVALUE
	MINVALUE 1
	NOCYCLE
	CACHE 20
	NOORDER;
	
create or replace trigger TRG_PICKLISTS
	BEFORE INSERT ON PICKLISTS
	REFERENCING OLD AS OLD NEW AS NEW
	FOR EACH ROW

DECLARE
  x INTEGER;
BEGIN
  IF :new.ID is null THEN
    SELECT SEQ_PICKLISTS.NEXTVAL INTO x FROM DUAL;     :new.ID := x;
  END IF;
END; Received on Wed Jun 12 2002 - 15:27:53 CDT