select * from orders;
select * from inventory ;
-----------------------------------------------------------------------------------
create table orders
(
order_id number(5) primary key ,
item_id number(5),
quantity number(5),
cost_per_unit number(6,2),
total_cost number(8,2),
transaction_date date
);
select * from orders;
-----------------------------------------------------------------------------------
create or replace trigger t_insert before insert
on orders for each row
begin
:new.total_cost := :[Link] * :new.cost_per_unit ;
:new.transaction_date := sysdate ;
end;
-----------------------------------------------------------------------------------
insert into orders(order_id , item_id , quantity , cost_per_unit)
values(1 , 100 , 5 , 10 );
-----------------------------------------------------------------------------------
create table inventory
(
item_id number(5) primary key ,
available_q number(5)
);
-----------------------------------------------------------------------------------
create or replace trigger t_update after insert
on orders for each row
begin
update inventory
set available_q = available_q - :[Link]
where item_id = :new.item_id ;
end;
-----------------------------------------------------------------------------------
insert into inventory ( item_id , available_q )
values(10 , 100);
-----------------------------------------------------------------------------------
( dual ? , floor ? )
create or replace trigger t_delete before delete
on orders for each row
declare
v_day number;
begin
select floor( sysdate - :old.transaction_date) into v_day from dual
if(v_day > 2) then
raise_application_error(-20007, ' the time is more than two day , your cant
cancelled ');
else
update inventory
set available_q = available_q + :[Link]
where item_id = :old.item_id;
end if;
end;
-----------------------------------------------------------------------------------
delete from orders where order_id = 3;
-----------------------------------------------------------------------------------
create table auditor
(
user_name varchar2(20) , event varchar2(20)
);
-----------------------------------------------------------------------------------
create or replace trigger t_order after insert or update or delete
on orders
declare
u_name varchar(20);
begin
select user into u_name from dual;
if inserting then
insert into auditor
values(u_name , 'inserting');
elsif updating then
insert into auditor
values(u_name , 'updating');
elsif deleting then
insert into auditor
values(u_name , 'deleting');
end if;
end;
-----------------------------------------------------------------------------------
alter trigger t_order disable;
-----------------------------------------------------------------------------------
alter trigger t_order enable;
-----------------------------------------------------------------------------------
drop trigger t_order;
-----------------------------------------------------------------------------------
* how to create index
create index index_name table_name(column_name);
-----------------------------------------------------------------------------------
create table mohammed
(
id number primary key using index (create index i_m on mohammed(id)) ,
name char
);
-----------------------------------------------------------------------------------
aliiiiiikk