Assignment Points: 40 1. Using the TAL database create a sequence number startin

Question

Assignment Points: 40 1. Using the TAL database create a sequence number starting with 10000000 with a max value of 1000000000 and will increase by 5, use the nocache option. 2. Create a trigger on the orderline table that will update the onhand gty of the item table based on an order line item being inserted, updated or deleted. When the order line is created then the onhand gty in the item table will be decremented by the number ordered. When the orderline table is updated then the onhand gty of the item will be incremented or decremented based on the number ordered. When the orderline is deleted then increment the onhand qty in the item table. 3. Create 2 audit tables based on the orderline and item table. Entries for these 2 tables will be added to when the orderline and item table are inserted updated and deleted (Triggers will do this). 4. Using the Tal database create 2 indexes for each table, one to help provide performance and the other to enforce uniqueness.

Explanation / Answer

I am giving these answer using oracle plsql developer:-

Ans 1. Creating a sequence in TAL database:-

CREATE SEQUENCE TAL_SEQUENCE

MINVALUE 10000000

MAXVALUE 1000000000

START WITH 10000000

INCREMENT BY 5

NOCACHE;

After running this command you can get the next sequence by using TAL_SEQUENCE.NEXTVAL;

Ans 2.

Here i have created all the trigger:-

1. insert after trigger -

create or replace trigger order_line_after_insert -- this is the name of trigger
after insert -- this trigger is after insert because we have to crete trigger when we have inserted a row in order_line table
on order_line -- this is the table name on which trigger is created
for each row -- this is use for every line which will insert into order-line
declare -- this is used for declaring variables. here we have declared three variable which i will use in further section.
num_ordered_new number(5);
item_num_new varchar2(10);
on_hand_new number(5);
begin -- this is section where we write all our sql query.
-- here i have stored the num_ordered and item_num valued from item_line table.
select num_ordered into num_ordered_new,
item_num into item_num_new
from dual;

-- now i will get the on_hand value which is currently available in item table.
select on_hand into on_hand_new
from item where item_num=item_num_new;

-- now i will update on_hand value with new one on the item table based on item_num
update item
set> where item_num=item_num_new;

end;

/

2. After update trigger

create or replace trigger order_line_after_update -- this is the name of trigger
after update -- this trigger is after update because we have to crete trigger when we have updated a row in order_line table
on order_line -- this is the table name on which trigger is created
for each row -- this is use for every which will insert into order-line
declare -- this is used for declaring variables. here we have declared three variable which i will use in further section.
num_ordered_new number(5);
item_num_new varchar2(10);
on_hand_new number(5);
begin -- this is section where we write all our sql query.
-- here i have stored the num_ordered and item_num valued from item_line table.
select num_ordered into num_ordered_new,
item_num into item_num_new
from dual;

-- now i will get the on_hand value which is currently available in item table.
select on_hand into on_hand_new
from item where item_num=item_num_new;

-- now i will update on_hand value with new one on the item table based on item_num
update item
set> where item_num=item_num_new;

end;

/

3. After delete trigger:-

create or replace trigger order_line_after_delete -- this is the name of trigger
after delete -- this trigger is after delete because we have to crete trigger when we have deleted a row in order_line table
on order_line -- this is the table name on which trigger is created
for each row -- this is use for every which will insert into order-line
declare -- this is used for declaring variables. here we have declared three variable which i will use in further section.
num_ordered_new number(5);
item_num_new varchar2(10);
on_hand_new number(5);
begin -- this is section where we write all our sql query.
-- here i have stored the num_ordered and item_num valued from item_line table.
select num_ordered into num_ordered_new,
item_num into item_num_new
from dual;

-- now i will get the on_hand value which is currently available in item table.
select on_hand into on_hand_new
from item where item_num=item_num_new;

-- now i will update on_hand value with new one on the item table based on item_num
update item
set> where item_num=item_num_new;

end;

/

Ans 3.

For this question first you will create two audit table. i assume that you know how table will created in database.

After that you have to create three trigger on item table and update the three trigger which i have created on 2nd question answer. I have added the updated trigger for this question and i assume that similarlly you will able to create trigger on item table for inseting data:-

1. updated After Insert trigger:-

create or replace trigger order_line_after_insert -- this is the name of trigger
after insert -- this trigger is after insert because we have to crete trigger when we have inserted a row in order_line table
on order_line -- this is the table name on which trigger is created
for each row -- this is use for every line which will insert into order-line
declare -- this is used for declaring variables. here we have declared three variable which i will use in further section.
num_ordered_new number(5);
item_num_new varchar2(10);
on_hand_new number(5);
begin -- this is section where we write all our sql query.
-- here i have stored the num_ordered and item_num valued from item_line table.
select num_ordered into num_ordered_new,
item_num into item_num_new
from dual;

-- now i will get the on_hand value which is currently available in item table.
select on_hand into on_hand_new
from item where item_num=item_num_new;

-- now i will update on_hand value with new one on the item table based on item_num
update item
set> where item_num=item_num_new;

-- updated trigger for third question answer in which we have to insert data into audit table
-- here i have written the insert query assuming a sample table order_line_audit which have two
--column (order_id, quantity)in which you have to insert audit data.
-- you have to update this query according to your table

insert into order_line_audit
(order_id, quantity)
values
(item_num,number_ordered_new);
end;

/

2. updated After Update Trigger:-

create or replace trigger order_line_after_update -- this is the name of trigger
after update -- this trigger is after update because we have to crete trigger when we have updated a row in order_line table
on order_line -- this is the table name on which trigger is created
for each row -- this is use for every which will insert into order-line
declare -- this is used for declaring variables. here we have declared three variable which i will use in further section.
num_ordered_new number(5);
item_num_new varchar2(10);
on_hand_new number(5);
begin -- this is section where we write all our sql query.
-- here i have stored the num_ordered and item_num valued from item_line table.
select num_ordered into num_ordered_new,
item_num into item_num_new
from dual;

-- now i will get the on_hand value which is currently available in item table.
select on_hand into on_hand_new
from item where item_num=item_num_new;

-- now i will update on_hand value with new one on the item table based on item_num
update item
set> where item_num=item_num_new;

end;

/

3. After delete trigger:-

create or replace trigger order_line_after_delete -- this is the name of trigger
after delete -- this trigger is after delete because we have to crete trigger when we have deleted a row in order_line table
on order_line -- this is the table name on which trigger is created
for each row -- this is use for every which will insert into order-line
declare -- this is used for declaring variables. here we have declared three variable which i will use in further section.
num_ordered_new number(5);
item_num_new varchar2(10);
on_hand_new number(5);
begin -- this is section where we write all our sql query.
-- here i have stored the num_ordered and item_num valued from item_line table.
select num_ordered into num_ordered_new,
item_num into item_num_new
from dual;

-- now i will get the on_hand value which is currently available in item table.
select on_hand into on_hand_new
from item where item_num=item_num_new;

-- now i will update on_hand value with new one on the item table based on item_num
update item
set> where item_num=item_num_new;

-- updated trigger for third question answer in which we have to insert data into audit table
-- here i have written the insert query assuming a sample table order_line_audit which have two
--column (order_id, quantity)in which you have to insert audit data.
-- you have to update this query according to your table

insert into order_line_audit
(order_id, quantity)
values
(item_num,number_ordered_new);
end;

/

3. updated after delete trigger:-

create or replace trigger order_line_after_delete -- this is the name of trigger
after delete -- this trigger is after delete because we have to crete trigger when we have deleted a row in order_line table
on order_line -- this is the table name on which trigger is created
for each row -- this is use for every which will insert into order-line
declare -- this is used for declaring variables. here we have declared three variable which i will use in further section.
num_ordered_new number(5);
item_num_new varchar2(10);
on_hand_new number(5);
begin -- this is section where we write all our sql query.
-- here i have stored the num_ordered and item_num valued from item_line table.
select num_ordered into num_ordered_new,
item_num into item_num_new
from dual;

-- now i will get the on_hand value which is currently available in item table.
select on_hand into on_hand_new
from item where item_num=item_num_new;

-- now i will update on_hand value with new one on the item table based on item_num
update item
set> where item_num=item_num_new;

-- updated trigger for third question answer in which we have to insert data into audit table
-- here i have written the insert query assuming a sample table order_line_audit which have two
--column (order_id, quantity)in which you have to insert audit data.
-- you have to update this query according to your table

insert into order_line_audit
(order_id, quantity)
values
(item_num,number_ordered_new);
end;

/

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.