I can't create a trigger in the following code. The one in there now never says trigger created and just goes on and on. Its keeps giving me the prompt.

I posted all the code but the problem area is in Bold.

::insert code::


Statement of Work
A database design to meet the current and future needs of Sunshine Railing a specialized aluminum and iron railing company. Railing requires a database to capture job contracts for railings. After accepting a contract personnel assign all customers unique customer numbers and all contracts unique job numbers. Customer can have multiple addresses for billing purposes. Customers can have a job contract with multiple locations. To capture the material cost of jobs a price list is associated with a material numbers. The database capturing job information in a one location will provide a tool to easily see what jobs are outstanding for what customers. In addition, the database can provide the location of jobs.

Database Design

The database will run on a Microsoft 2000.

The database will be an Oracle v8.1.6

The DDL and DML will be SQL.

The UMUC nova system will be used.



SQL> SPOOL ON
SQL>
SQL> Create Table Customer
2 (Cust_NO char(10) NOT NULL,
3 Cust_Name varchar2(30) NOT NULL,
4 Constraint pk_Customer primary key (Cust_No)
5 )
6 ;

Table created.

SQL>
SQL> create table Address
2 (Addr_ID char(10) Not NuLL,
3 Addr_Street varchar2(30) NOT NULL,
4 Addr_City char (30) NOT NULL,
5 Addr_State char (30),
6 Addr_Zip number (11),
7 Constraint pk_Address primary key (Addr_ID)
8 )
9 ;

Table created.

SQL>
SQL> Create Table Material
2 (
3 Mat_No varchar2 (30) NOT NULL,
4 Mat_Name varchar2 (30) NOT NULL,
5 Constraint pk_Material primary key (Mat_No)
6 )
7 ;

Table created.

SQL>
SQL> Create Table Location
2 (Loc_ID char(10) Not NULL,
3 Loc_Street varchar2(30) NOT NULL,
4 Loc_City char (30) NOT NULL,
5 Loc_State char(30),
6 Loc_Zip number (11),
7 Constraint pk_Location primary key (Loc_ID)
8 )
9 ;

Table created.

SQL>
SQL>
SQL> Create Table Price
2 (P_Cost decimal (6,2),
3 P_Markup varchar2 (15),
4 Mat_No varchar2 (30) NOT NULL,
5 Constraint fk_Material_No foreign key (Mat_No) references Material
6 )
7 ;

Table created.

SQL>
SQL>
SQL> Create Table Job
2 (Job_No char (30) NOT NULL,
3 Job_Cost decimal (2,6) NOT NULL,
4 Cust_No char (10) NOT NULL,
5 Mat_No varchar2(30) NOT NULL,
6 Constraint pk_Job primary key (Job_No),
7 Constraint fk_Customer foreign key (Cust_No) references Customer,
8 Constraint fk_Material foreign key (Mat_No) references Material
9 )
10 ;

Table created.

SQL>
SQL> drop Table Job;

Table dropped.

SQL>
SQL> drop Table Price;

Table dropped.

SQL>
SQL> Drop Table Location;

Table dropped.

SQL>
SQL> Drop Table Material;

Table dropped.

SQL>
SQL> Drop Table Address;

Table dropped.

SQL>
SQL> Drop Table Customer;

Table dropped.

SQL>
SQL> Create Table Customer
2 (Cust_NO char(10) NOT NULL,
3 Cust_Name varchar2(30) NOT NULL,
4 Constraint pk_Customer primary key (Cust_No)
5 )
6 ;

Table created.

SQL>
SQL> create table Address
2 (Addr_ID char(10) Not NuLL,
3 Addr_Street varchar2(30) NOT NULL,
4 Addr_City char (30) NOT NULL,
5 Addr_State char (30),
6 Addr_Zip number (11),
7 Constraint pk_Address primary key (Addr_ID)
8 )
9 ;

Table created.

SQL>
SQL> Create Table Material
2 (
3 Mat_No varchar2 (30) NOT NULL,
4 Mat_Name varchar2 (30) NOT NULL,
5 Constraint pk_Material primary key (Mat_No)
6 )
7 ;

Table created.

SQL>
SQL> Create Table Location
2 (Loc_ID char(10) Not NULL,
3 Loc_Street varchar2(30) NOT NULL,
4 Loc_City char (30) NOT NULL,
5 Loc_State char(30),
6 Loc_Zip number (11),
7 Constraint pk_Location primary key (Loc_ID)
8 )
9 ;

Table created.

SQL>
SQL>
SQL> Create Table Price
2 (P_Cost decimal (6,2),
3 P_Markup varchar2 (15),
4 Mat_No varchar2 (30) NOT NULL,
5 Constraint fk_Material_No foreign key (Mat_No) references Material
6 )
7 ;

Table created.

SQL>
SQL>
SQL> Create Table Job
2 (Job_No char (30) NOT NULL,
3 Job_Cost decimal (2,6) NOT NULL,
4 Cust_No char (10) NOT NULL,
5 Mat_No varchar2(30) NOT NULL,
6 Constraint pk_Job primary key (Job_No),
7 Constraint fk_Customer foreign key (Cust_No) references Customer,
8 Constraint fk_Material foreign key (Mat_No) references Material
9 )
10 ;

Table created.

SQL>
SQL>
SQL> Create view Job_view as
2 select Job_No, Job_Cost,Cust_No
3 from Job
4 Where Mat_NO = '*'
5 ;

View created.

SQL>
SQL> Create view location_view as
2 select Loc_street, Loc_City, Loc_State
3 from location
4 where Loc_state ='fl'
5 ;

View created.

SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> Create index fk_Material
2 on Job (Mat_No)
3 ;

Index created.

SQL>
SQL>
SQL> Create index fk_Customer
2 on Job (Cust_No)
3 ;

Index created.

SQL>
SQL> Create index fk_Material_No
2 on Price (Mat_No)
3 ;

Index created.

SQL>
SQL>
SQL>
SQL> select substr(object_name, 1, 20), object_type, status
2 from user_objects
3 ;

SUBSTR(OBJECT_NAME,1 OBJECT_TYPE STATUS
-------------------- ------------------ -------
ADDRESS TABLE VALID
AQ$DEF$_AQCALL VIEW VALID
AQ$DEF$_AQERROR VIEW VALID
AQ$_DEF$_AQCALL_E QUEUE VALID
AQ$_DEF$_AQERROR_E QUEUE VALID
AQ$_QUEUES TABLE VALID
AQ$_QUEUES_CHECK INDEX VALID
AQ$_QUEUES_PRIMARY INDEX VALID
AQ$_QUEUE_TABLES TABLE VALID
AQ$_QUEUE_TABLES_PRI INDEX VALID
AQ$_SCHEDULES TABLE VALID

SUBSTR(OBJECT_NAME,1 OBJECT_TYPE STATUS
-------------------- ------------------ -------
AQ$_SCHEDULES_CHECK INDEX VALID
AQ$_SCHEDULES_PRIMAR INDEX VALID
CATALOG SYNONYM VALID
COL SYNONYM VALID
CUSTOMER TABLE VALID
CUSTOMER_ADDRESS TABLE VALID
DBMS_REPCAT_AUTH PACKAGE VALID
DBMS_REPCAT_AUTH PACKAGE BODY VALID
DEF$_AQCALL TABLE VALID
DEF$_AQCALL QUEUE VALID
DEF$_AQERROR TABLE VALID

SUBSTR(OBJECT_NAME,1 OBJECT_TYPE STATUS
-------------------- ------------------ -------
DEF$_AQERROR QUEUE VALID
DEF$_CALLDEST TABLE VALID
DEF$_CALLDEST_N2 INDEX VALID
DEF$_CALLDEST_PRIMAR INDEX VALID
DEF$_DEFAULTDEST TABLE VALID
DEF$_DEFAULTDEST_PRI INDEX VALID
DEF$_DESTINATION TABLE VALID
DEF$_DESTINATION_PRI INDEX VALID
DEF$_ERROR TABLE VALID
DEF$_ERROR_PRIMARY INDEX VALID
DEF$_LOB TABLE VALID

SUBSTR(OBJECT_NAME,1 OBJECT_TYPE STATUS
-------------------- ------------------ -------
DEF$_LOB_N1 INDEX VALID
DEF$_LOB_PRIMARY INDEX VALID
DEF$_ORIGIN TABLE VALID
DEF$_PROPAGATOR TABLE VALID
DEF$_PROPAGATOR_PRIM INDEX VALID
DEF$_PROPAGATOR_TRIG TRIGGER VALID
DEF$_PUSHED_TRANSACT TABLE VALID
DEF$_PUSHED_TRAN_PRI INDEX VALID
DEF$_TEMP$LOB TABLE VALID
DEF$_TRANORDER INDEX VALID
FK_CUSTOMER INDEX VALID

SUBSTR(OBJECT_NAME,1 OBJECT_TYPE STATUS
-------------------- ------------------ -------
FK_MATERIAL INDEX VALID
FK_MATERIAL_NO INDEX VALID
HELP TABLE VALID
HELP_TOPIC_SEQ INDEX VALID
I_REPCAT$_SNAPGROUP1 INDEX VALID
JOB TABLE VALID
JOB_VIEW VIEW VALID
LOCATION TABLE VALID
LOCATION_VIEW VIEW VALID
MATERIAL TABLE VALID
ORA$_SYS_REP_AUTH PROCEDURE VALID

SUBSTR(OBJECT_NAME,1 OBJECT_TYPE STATUS
-------------------- ------------------ -------
PK_ADDRESS INDEX VALID
PK_CUSTOMER INDEX VALID
PK_CUSTOMER_ADDRESS INDEX VALID
PK_JOB INDEX VALID
PK_LOCATION INDEX VALID
PK_MATERIAL INDEX VALID
PRICE TABLE VALID
PRODUCT_PRIVS VIEW VALID
PRODUCT_USER_PROFILE SYNONYM VALID
PUBLICSYN SYNONYM VALID
REPCAT$_AUDIT_ATTRIB TABLE VALID

SUBSTR(OBJECT_NAME,1 OBJECT_TYPE STATUS
-------------------- ------------------ -------
REPCAT$_AUDIT_ATTRIB INDEX VALID
REPCAT$_AUDIT_COLUMN TABLE VALID
REPCAT$_AUDIT_COLUMN INDEX VALID
REPCAT$_COLUMN_GROUP TABLE VALID
REPCAT$_COLUMN_GROUP INDEX VALID
REPCAT$_CONFLICT TABLE VALID
REPCAT$_CONFLICT_PK INDEX VALID
REPCAT$_DDL TABLE VALID
REPCAT$_DDL INDEX VALID
REPCAT$_FLAVORS TABLE VALID
REPCAT$_FLAVORS_FNAM INDEX VALID

SUBSTR(OBJECT_NAME,1 OBJECT_TYPE STATUS
-------------------- ------------------ -------
REPCAT$_FLAVORS_GNAM INDEX VALID
REPCAT$_FLAVORS_S SEQUENCE VALID
REPCAT$_FLAVORS_UNQ1 INDEX VALID
REPCAT$_FLAVOR_OBJEC TABLE VALID
REPCAT$_FLAVOR_OBJEC INDEX VALID
REPCAT$_FLAVOR_OBJEC INDEX VALID
REPCAT$_GENERATED TABLE VALID
REPCAT$_GENERATED_N1 INDEX VALID
REPCAT$_GROUPED_COLU TABLE VALID
REPCAT$_GROUPED_COLU INDEX VALID
REPCAT$_KEY_COLUMNS TABLE VALID

SUBSTR(OBJECT_NAME,1 OBJECT_TYPE STATUS
-------------------- ------------------ -------
REPCAT$_KEY_COLUMNS_ INDEX VALID
REPCAT$_OBJECT_PARMS TABLE VALID
REPCAT$_OBJECT_PARMS INDEX VALID
REPCAT$_PARAMETER_CO TABLE VALID
REPCAT$_PARAMETER_CO INDEX VALID
REPCAT$_PRIORITY TABLE VALID
REPCAT$_PRIORITY_GRO TABLE VALID
REPCAT$_PRIORITY_GRO INDEX VALID
REPCAT$_PRIORITY_GRO INDEX VALID
REPCAT$_PRIORITY_PK INDEX VALID
REPCAT$_REFRESH_TEMP TABLE VALID

SUBSTR(OBJECT_NAME,1 OBJECT_TYPE STATUS
-------------------- ------------------ -------
REPCAT$_REFRESH_TEMP INDEX VALID
REPCAT$_REFRESH_TEMP SEQUENCE VALID
REPCAT$_REFRESH_TEMP INDEX VALID
REPCAT$_REPCAT TABLE VALID
REPCAT$_REPCATLOG TABLE VALID
REPCAT$_REPCATLOG_GN INDEX VALID
REPCAT$_REPCATLOG_PR INDEX VALID
REPCAT$_REPCAT_PRIMA INDEX VALID
REPCAT$_REPCOLUMN TABLE VALID
REPCAT$_REPCOLUMN_PK INDEX VALID
REPCAT$_REPCOLUMN_UK INDEX VALID

SUBSTR(OBJECT_NAME,1 OBJECT_TYPE STATUS
-------------------- ------------------ -------
REPCAT$_REPGEN_PRIMA INDEX VALID
REPCAT$_REPGROUP_PRI TABLE VALID
REPCAT$_REPGROUP_PRI INDEX VALID
REPCAT$_REPGROUP_PRI INDEX VALID
REPCAT$_REPOBJECT TABLE VALID
REPCAT$_REPOBJECT_GN INDEX VALID
REPCAT$_REPOBJECT_PR INDEX VALID
REPCAT$_REPPROP TABLE VALID
REPCAT$_REPPROP_DBLI INDEX VALID
REPCAT$_REPPROP_KEY SEQUENCE VALID
REPCAT$_REPPROP_KEY_ INDEX VALID

SUBSTR(OBJECT_NAME,1 OBJECT_TYPE STATUS
-------------------- ------------------ -------
REPCAT$_REPPROP_PRIM INDEX VALID
REPCAT$_REPSCHEMA TABLE VALID
REPCAT$_REPSCHEMA_PR INDEX VALID
REPCAT$_RESOLUTION TABLE VALID
REPCAT$_RESOLUTION_M TABLE VALID
REPCAT$_RESOLUTION_P INDEX VALID
REPCAT$_RESOLUTION_S TABLE VALID
REPCAT$_RESOLUTION_S INDEX VALID
REPCAT$_RESOL_METHOD INDEX VALID
REPCAT$_RESOL_STATS_ TABLE VALID
REPCAT$_RESOL_STATS_ INDEX VALID

SUBSTR(OBJECT_NAME,1 OBJECT_TYPE STATUS
-------------------- ------------------ -------
REPCAT$_RUNTIME_PARM TABLE VALID
REPCAT$_RUNTIME_PARM INDEX VALID
REPCAT$_RUNTIME_PARM SEQUENCE VALID
REPCAT$_SNAPGROUP TABLE VALID
REPCAT$_TEMPLATE_OBJ TABLE VALID
REPCAT$_TEMPLATE_OBJ INDEX VALID
REPCAT$_TEMPLATE_OBJ INDEX VALID
REPCAT$_TEMPLATE_OBJ INDEX VALID
REPCAT$_TEMPLATE_OBJ INDEX VALID
REPCAT$_TEMPLATE_OBJ SEQUENCE VALID
REPCAT$_TEMPLATE_PAR TABLE VALID

SUBSTR(OBJECT_NAME,1 OBJECT_TYPE STATUS
-------------------- ------------------ -------
REPCAT$_TEMPLATE_PAR INDEX VALID
REPCAT$_TEMPLATE_PAR SEQUENCE VALID
REPCAT$_TEMPLATE_PAR INDEX VALID
REPCAT$_TEMPLATE_SIT TABLE VALID
REPCAT$_TEMPLATE_SIT INDEX VALID
REPCAT$_TEMPLATE_SIT SEQUENCE VALID
REPCAT$_TEMPLATE_SIT INDEX VALID
REPCAT$_TEMP_OUTPUT_ SEQUENCE VALID
REPCAT$_USER_AUTHORI TABLE VALID
REPCAT$_USER_AUTHORI INDEX VALID
REPCAT$_USER_AUTHORI SEQUENCE VALID

SUBSTR(OBJECT_NAME,1 OBJECT_TYPE STATUS
-------------------- ------------------ -------
REPCAT$_USER_AUTHORI INDEX VALID
REPCAT$_USER_PARM_VA TABLE VALID
REPCAT$_USER_PARM_VA INDEX VALID
REPCAT$_USER_PARM_VA SEQUENCE VALID
REPCAT$_USER_PARM_VA INDEX VALID
REPCATLOGTRIG TRIGGER VALID
REPCAT_LOG_SEQUENCE SEQUENCE VALID
SQLPLUS_PRODUCT_PROF TABLE VALID
SYSCATALOG SYNONYM VALID
SYSFILES SYNONYM VALID
SYS_C00745 INDEX VALID

SUBSTR(OBJECT_NAME,1 OBJECT_TYPE STATUS
-------------------- ------------------ -------
SYS_C00748 INDEX VALID
SYS_LOB0000002515C00 LOB VALID
SYS_LOB0000002673C00 LOB VALID
SYS_LOB0000002683C00 LOB VALID
SYS_LOB0000002701C00 LOB VALID
SYS_LOB0000002701C00 LOB VALID
SYS_LOB0000002701C00 LOB VALID
SYS_LOB0000002710C00 LOB VALID
SYS_LOB0000002710C00 LOB VALID
SYS_LOB0000002710C00 LOB VALID
SYS_LOB0000003440C00 LOB VALID

SUBSTR(OBJECT_NAME,1 OBJECT_TYPE STATUS
-------------------- ------------------ -------
SYS_LOB0000003453C00 LOB VALID
SYS_LOB0000003467C00 LOB VALID
SYS_LOB0000003490C00 LOB VALID
TAB SYNONYM VALID
TABLE_TWO TABLE VALID
TABQUOTAS SYNONYM VALID

182 rows selected.

SQL>
SQL> SPOOL OFF
SQL>
SQL>

SQL>
SQL>
SQL>

<======this is where I am having the problem!!!!!=========>

SQL> Create Table Material
2 (
3 Mat_No varchar2 (30) NOT NULL,
4 Mat_Name varchar2 (30) NOT NULL,
5 Constraint pk_Material primary key (Mat_No)
6 )
7 ;

Table created.

SQL>
SQL>
SQL>
SQL>
SQL>
SQL> Create trigger New_Material_trig;
2 after insert on Material
3 Referencing NEW AS newmaterial
4 for each row
5 Insert into Material (ADD MATERIAL NO):
6 end;
7
8 SPOOL OFF



:: end code ::


Any help will be appreicated


hjack