Possible Bug With Trigger DDL

Jamie · Mar 10, 2010

Hello,

I'm using version 8.0.0.1480 on Oracle 10g 10.2.0.3.0.

I put together a file of DDL statements for creating some objects in another schema. These included table definitions, along with sequences and triggers for automatically populating primary keys.

(See the file contentents below)

I was going through, entry by entry, placing the cursor in a statement and hitting F8 to execute it. This worked pretty well until I came to the first trigger definition.

When I tried to execute that, it executed the trigger, but included everthing from below the trigger as if it were part of it, resulting in a broken trigger.

I found out if I highlighted the trigger DDL statement first, I could force it to only execute that one statement. However, attempting to execute the next statement down (creating the next table) resulted in the same problem, with it thinking I want to code the trigger above (trans_summary_33_pk). Once again I had to rely on highlighting, and was eventually able to execute all statements.

So is this a bug in the editor, where it can't figure out where the trigger DDL stops? Or am I missing some sort of delimiter? Thanks.

File Contents In Question Follow:

-------------------------------------------------------
-- Create the TRANS_SUMMARY_33 table and associated sequences and triggers
-------------------------------------------------------

-- Create table
create table TRANS_SUMMARY_33
(
TRANS_SUMMARY_33_PK NUMBER(19) not null,
STATE_ID VARCHAR2(17) not null,
AMOUNT_RECEIVED NUMBER(19,2) not null,
NE_SALES_TAX NUMBER(19,2) not null,
PERIOD VARCHAR2(6) not null,
TAXCAT VARCHAR2(4) not null,
NE_CITY_SALES_TAX NUMBER(19,2) not null,
NE_CITY_SALES_TAX_FEE_DEDUCTED NUMBER(19,2) not null,
PENALTY_AND_INTEREST NUMBER(19,2) not null
)
tablespace GPS_DATA
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 1M
next 1M
minextents 1
maxextents unlimited
pctincrease 0
);
-- Create/Recreate primary, unique and foreign key constraints
alter table TRANS_SUMMARY_33
add constraint TRANS_SUMMARY_33_PK primary key (TRANS_SUMMARY_33_PK)
using index
tablespace GPS_DATA
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 1M
next 1M
minextents 1
maxextents unlimited
pctincrease 0
);

-- Create sequence
create sequence TRANS_SUMMARY_33_PK_SEQ
minvalue 1
maxvalue 999999999999999999999999999
start with 2573
increment by 1
cache 20;

CREATE OR REPLACE TRIGGER trans_summary_33_pk
BEFORE INSERT OR UPDATE ON trans_summary_33
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW

BEGIN
IF :new.trans_summary_33_pk IS NULL OR
:new.trans_summary_33_pk = 0
THEN
select trans_summary_33_pk_seq.NEXTVAL
INTO :new.trans_summary_33_pk
FROM sys.dual;
END IF;
END;

-------------------------------------------------------
-- Create the SCHED_SUMMARY_33 table and associated sequences and triggers
-------------------------------------------------------

-- Create table
create table SCHED_SUMMARY_33
(
SCHED_SUMMARY_33_PK NUMBER(19) not null,
TRANS_SUMMARY_33_FK NUMBER(19) not null,
CITY_CODE VARCHAR2(5) not null,
SALES_TAX NUMBER(19,2) not null
)
tablespace GPS_DATA
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 1M
next 1M
minextents 1
maxextents unlimited
pctincrease 0
);
-- Create/Recreate primary, unique and foreign key constraints
alter table SCHED_SUMMARY_33
add constraint SCHED_SUMMARY_33_PK primary key (SCHED_SUMMARY_33_PK)
using index
tablespace GPS_DATA
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 1M
next 1M
minextents 1
maxextents unlimited
pctincrease 0
);
alter table SCHED_SUMMARY_33
add constraint TRANS_SUMMARY_33_FK foreign key (TRANS_SUMMARY_33_FK)
references TRANS_SUMMARY_33 (TRANS_SUMMARY_33_PK);

-- Create sequence
create sequence SCHED_SUMMARY_33_PK_SEQ
minvalue 1
maxvalue 999999999999999999999999999
start with 4507
increment by 1
cache 20;

CREATE OR REPLACE TRIGGER sched_summary_33_pk
BEFORE INSERT OR UPDATE ON sched_summary_33
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW

BEGIN
IF :new.sched_summary_33_pk IS NULL OR
:new.sched_summary_33_pk = 0
THEN
select sched_summary_33_pk_seq.NEXTVAL
INTO :new.sched_summary_33_pk
FROM sys.dual;
END IF;
END;

-------------------------------------------------------
-- Create the ALLOC_BALFWD_STATE_33 table and associated sequences and triggers
-------------------------------------------------------

-- Create table
create table ALLOC_BALFWD_STATE_33
(
ALLOC_BALFWD_STATE_33_PK NUMBER(19) not null,
STATEID VARCHAR2(17) not null,
PROCPD VARCHAR2(6) not null,
TAX NUMBER(19,2) not null,
PENINT NUMBER(19,2) not null
)
tablespace GPS_DATA
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 1M
next 1M
minextents 1
maxextents unlimited
pctincrease 0
);
-- Create/Recreate primary, unique and foreign key constraints
alter table ALLOC_BALFWD_STATE_33
add constraint ALLOC_BALFWD_STATE_33_PK primary key (ALLOC_BALFWD_STATE_33_PK)
using index
tablespace GPS_DATA
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 1M
next 1M
minextents 1
maxextents unlimited
pctincrease 0
);
alter table ALLOC_BALFWD_STATE_33
add constraint UNIQUE_SID_AND_PERIOD unique (STATEID, PROCPD)
using index
tablespace GPS_DATA
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 1M
next 1M
minextents 1
maxextents unlimited
pctincrease 0
);

-- Create sequence
create sequence ALLOC_BALFWD_STATE_33_PK_SEQ
minvalue 1
maxvalue 999999999999999999999999999
start with 712598
increment by 1
cache 20;

create or replace trigger alloc_balfwd_state_33_pk
before insert or update on alloc_balfwd_state_33
for each row
begin
if :new.alloc_balfwd_state_33_pk is null or :new.alloc_balfwd_state_33_pk = 0 then
select alloc_balfwd_state_33_pk_seq.nextval
into :new.alloc_balfwd_state_33_pk
from sys.dual;
end if;
end;

-------------------------------------------------------
-- Create the ALLOC_BALFWD_CITY_33 table and associated sequences and triggers
-------------------------------------------------------

-- Create table
create table ALLOC_BALFWD_CITY_33
(
ALLOC_BALFWD_CITY_33_PK NUMBER(19) not null,
ALLOC_BALFWD_STATE_33_FK NUMBER(19) not null,
CITY_CODE VARCHAR2(3) not null,
TAX NUMBER(19,2) not null
)
tablespace GPS_DATA
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 1M
next 1M
minextents 1
maxextents unlimited
pctincrease 0
);
-- Create/Recreate primary, unique and foreign key constraints
alter table ALLOC_BALFWD_CITY_33
add constraint ALLOC_BALFWD_CITY_33_PK primary key (ALLOC_BALFWD_CITY_33_PK)
using index
tablespace GPS_DATA
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 1M
next 1M
minextents 1
maxextents unlimited
pctincrease 0
);
alter table ALLOC_BALFWD_CITY_33
add constraint UNIQUE_CITY_AND_PERIOD unique (ALLOC_BALFWD_STATE_33_FK, CITY_CODE)
using index
tablespace GPS_DATA
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 1M
next 1M
minextents 1
maxextents unlimited
pctincrease 0
);
alter table ALLOC_BALFWD_CITY_33
add constraint ALLOC_BALFWD_STATE_33_FK foreign key (ALLOC_BALFWD_STATE_33_FK)
references ALLOC_BALFWD_STATE_33 (ALLOC_BALFWD_STATE_33_PK);

-- Create sequence
create sequence ALLOC_BALFWD_CITY_33_PK_SEQ
minvalue 1
maxvalue 999999999999999999999999999
start with 171159
increment by 1
cache 20;

create or replace trigger alloc_balfwd_city_33_pk
before insert or update on alloc_balfwd_city_33
for each row
begin
if :new.alloc_balfwd_city_33_pk is null or :new.alloc_balfwd_city_33_pk = 0 then
select alloc_balfwd_city_33_pk_seq.nextval
into :new.alloc_balfwd_city_33_pk
from sys.dual;
end if;
end;

-------------------------------------------------------
-- Create the ALLOC_AUDIT_33 table and associated sequences and triggers
-------------------------------------------------------

-- Create table
create table ALLOC_AUDIT_33
(
ALLOC_AUDIT_33_PK NUMBER(19) not null,
STATEID VARCHAR2(17),
PROCPD VARCHAR2(6),
TAXCAT VARCHAR2(4),
CTYCD VARCHAR2(4),
BEGBAL_SL NUMBER(19,2),
ALLOC_SL NUMBER(19,2),
ENDBAL_SL NUMBER(19,2),
BEGBAL_PI NUMBER(19,2),
ALLOC_PI NUMBER(19,2),
ENDBAL_PI NUMBER(19,2)
)
tablespace GPS_DATA
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 1M
next 1M
minextents 1
maxextents unlimited
pctincrease 0
);
-- Create/Recreate primary, unique and foreign key constraints
alter table ALLOC_AUDIT_33
add constraint ALLOC_AUDIT_33_PK primary key (ALLOC_AUDIT_33_PK)
using index
tablespace GPS_DATA
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 1M
next 1M
minextents 1
maxextents unlimited
pctincrease 0
);

-- Create sequence
create sequence ALLOC_AUDIT_33_PK_SEQ
minvalue 1
maxvalue 999999999999999999999999999
start with 11631
increment by 1
cache 20;

create or replace trigger alloc_audit_33_pk
before insert or update on alloc_audit_33
for each row
begin
if :new.alloc_audit_33_pk is null or :new.alloc_audit_33_pk = 0 then
select alloc_audit_33_pk_seq.nextval
into :new.alloc_audit_33_pk
from sys.dual;
end if;
end;

Theod · Mar 11, 2010

Hi,

use / as delimiter after ;

Jamie · Mar 11, 2010

Hi,

I just tried putting in slashes after the semicolons, but it doesn't seem to have any effect.

orca777 · Mar 12, 2010

Trigger/Types and PL/SQL objects must be
finished with '/' to get executed/defined via SQL*PLUS
The last ';' on these objects belongs to the Code of the object itself.
you should execute scripts with the command window which is a SQL*PLUS emulator
/Karl

just try your code via SQL*PLUS too
if it does not work either PL/DeV is clean

/Karl

Jamie · Mar 15, 2010

Okay. I understand now that I can fix it by using slashes in SQL*PLUS mode. But I am still curious as to why it doesn't work AS IS in the regular SQL Window, when plenty of other SQL DDL statements do.

I'm not sure why the parser doesn't understand that the "END;" is the end of the trigger's code. If someone could explain that, or point me to a site that does, I'd be quite appreciative.

In any case, Thanks.

Possible Bug With Trigger DDL

Jamie

Member

Theod

Member³

Jamie

Member

orca777

Member³

Jamie

Member

Similar threads