要求:实现row database triggers来使表格Part里面的total与表格里OrderLine的信息一致。
CREATE TABLE Part( /* Part description */
PNumber NUMBER(10) NOT NULL, /* Number */
PName VARCHAR(30) NOT NULL, /* Name */
PManufacturer VARCHAR(255) NOT NULL, /* Manufacturer */
PPrice NUMBER(7,2) NOT NULL, /* Price */
PRating VARCHAR(10) NULL, /* Rating */
Total NUMBER(6) NULL, /*Total number*/
CONSTRAINT Part_pkey PRIMARY KEY( PNumber ),
CONSTRAINT Part_fkey1 FOREIGN KEY( PRating )
);
CREATE TABLE Orders( /* Order description */
ONumber NUMBER(10) NOT NULL, /* Number */
ODate DATE NOT NULL, /* Date when issued */
OCustomer VARCHAR(255) NOT NULL, /* Customer involved */
CONSTRAINT Orders_pkey PRIMARY KEY( ONumber )
);
CREATE TABLE OrderLine( /* Ordered parts */
LOrder NUMBER(10) NOT NULL, /* Order number */
LLine NUMBER(3) NOT NULL, /* Line number */
LPart NUMBER(10) NOT NULL, /* Part number */
LQuantity NUMBER(6) NOT NULL, /* Quantity */
CONSTRAINT OrderLine_pkey PRIMARY KEY( LOrder, LLine ),
CONSTRAINT OrderLine_fkey1 FOREIGN KEY( LPart )
REFERENCES Part( PNumber ),
CONSTRAINT OrderLine_fkey2 FOREIGN KEY( LOrder )
REFERENCES Orders( ONumber )
);
CREATE TABLE Part( /* Part description */
PNumber NUMBER(10) NOT NULL, /* Number */
PName VARCHAR(30) NOT NULL, /* Name */
PManufacturer VARCHAR(255) NOT NULL, /* Manufacturer */
PPrice NUMBER(7,2) NOT NULL, /* Price */
PRating VARCHAR(10) NULL, /* Rating */
Total NUMBER(6) NULL, /*Total number*/
CONSTRAINT Part_pkey PRIMARY KEY( PNumber ),
CONSTRAINT Part_fkey1 FOREIGN KEY( PRating )
);
CREATE TABLE Orders( /* Order description */
ONumber NUMBER(10) NOT NULL, /* Number */
ODate DATE NOT NULL, /* Date when issued */
OCustomer VARCHAR(255) NOT NULL, /* Customer involved */
CONSTRAINT Orders_pkey PRIMARY KEY( ONumber )
);
CREATE TABLE OrderLine( /* Ordered parts */
LOrder NUMBER(10) NOT NULL, /* Order number */
LLine NUMBER(3) NOT NULL, /* Line number */
LPart NUMBER(10) NOT NULL, /* Part number */
LQuantity NUMBER(6) NOT NULL, /* Quantity */
CONSTRAINT OrderLine_pkey PRIMARY KEY( LOrder, LLine ),
CONSTRAINT OrderLine_fkey1 FOREIGN KEY( LPart )
REFERENCES Part( PNumber ),
CONSTRAINT OrderLine_fkey2 FOREIGN KEY( LOrder )
REFERENCES Orders( ONumber )
);
1)如果Total = 0, then NULL
2) 如果Total > 0和<=100, then 'RARE'
3) 如果Total > 100和<=1000, then 'NORMAL'
4) ELSE then 'FREQUENT'描述:implement the database triggers that enforce the consistency constraint listed above when information about the parts ordered by a customer is entered into the database and when information about a new part is added to a database.Then implement SQL script that comprehensively tests the triggers implemented in last step.拜托大家了。
of units of each part ordered by the customers so far and that sets a correct value of
the total number of ordered units for each part immediately after the execution of a script a2create.sql.
(ii) Implement the row database triggers that keep the total number of ordered units consistent with information included in a relational table OrderLine.
(iii) Implement SQL script that comprehensively tests the triggers implemented in step
(i) Implement SQL statement that creates a relational view VFLine(VONumber, VLNUmber, VPNumber, VPName, VQuantity) that contains information about the parts (VPNumber, VPName) frequently ordered by the customers, order numbers (VONumber), line numbers (VLNUmber), and quantities
(VQuantity).
(ii) Implement a database trigger that allows for the insertions of data into the relational view VFLine and later on into the database. If an insertion is not related to a part frequently ordered by the customers then such insertion should be rejected.
(iii) Implement SQL script that comprehensively tests a trigger implemented in step (ii).
PNumber NUMBER(10) NOT NULL, /* Number */
PName VARCHAR(30) NOT NULL, /* Name */
PManufacturer VARCHAR(255) NOT NULL, /* Manufacturer */
PPrice NUMBER(7,2) NOT NULL, /* Price */
PRating VARCHAR(10) NULL, /* Rating */
Total NUMBER(6) NULL, /*Total number*/
CONSTRAINT Part_pkey PRIMARY KEY( PNumber ),
CONSTRAINT Part_fkey1 FOREIGN KEY( PRating )
);
CREATE TABLE Orders( /* Order description */
ONumber NUMBER(10) NOT NULL, /* Number */
ODate DATE NOT NULL, /* Date when issued */
OCustomer VARCHAR(255) NOT NULL, /* Customer involved */ CONSTRAINT Orders_pkey PRIMARY KEY( ONumber )
); CREATE TABLE OrderLine( /* Ordered parts */
LOrder NUMBER(10) NOT NULL, /* Order number */
LLine NUMBER(3) NOT NULL, /* Line number */
LPart NUMBER(10) NOT NULL, /* Part number */
LQuantity NUMBER(6) NOT NULL, /* Quantity */ CONSTRAINT OrderLine_pkey PRIMARY KEY( LOrder, LLine ),
CONSTRAINT OrderLine_fkey1 FOREIGN KEY( LPart ) REFERENCES Part( PNumber ),
CONSTRAINT OrderLine_fkey2 FOREIGN KEY( LOrder ) REFERENCES Orders( ONumber )
);
INSTEAD OF 选项使ORACLE激活触发器,而不执行触发事件。只能对视图和对象视图建立INSTEAD OF触发器,而不能对表、模式和数据库建立INSTEAD OF 触发器。
CREATE OR REPLACE TRIGGER emp_view_delete
INSTEAD OF DELETE ON emp_view FOR EACH ROW
BEGIN
DELETE FROM emp WHERE deptno= :old.deptno;
END emp_view_delete;