create database ItemManager01;

use ItemManager01;

create table Customer(

cID int constraint PK_Customer

primary key,

cName nvarchar(30),

cAge int

create table [Order](

oID int primary key,

cID int constraint FK_Customer

references Customer,

oDate datetime,

oTotalPrice int

create table Product(

pID int constraint PK_Product

primary key,

pName nvarchar(35),

pPrice int

create table OrderDetail(

oID int constraint FK_Order

references [Order],

pID int constraint FK_Product

references Product,

odQty int,

constraint FK_OrderDetail

primary key (oID, pID)

--chen du lieu

insert into Customer values (1,N'Minh Quân',10);

insert into Customer values (2,N'Ngọc Oanh',20);

insert into Customer values (3,N'Hồng Hà',50);

select * from Customer;

insert into [Order] values (1,1,'3/21/2006',NULL);

insert into [Order] values (2,2,'3/23/2006',NULL);

insert into [Order] values (3,1,'3/16/2006',NULL);

select * from [Order];

insert into Product values (1,'May Giat',3);

insert into Product values (2,'Dieu hoa',5);

insert into Product values (3,'Tu lanh',7);

insert into Product values (4,'Quat',1);

insert into Product values (5,'Bep dien',2);

select * from PRoduct;

insert into OrderDetail values (1,1,3);

insert into OrderDetail values (1,3,7);

insert into OrderDetail values (1,4,2);

insert into OrderDetail values (2,1,1);

insert into OrderDetail values (3,1,8);

insert into OrderDetail values (2,5,4);

insert into OrderDetail values (2,3,3);

select * from OrderDetail;

--cau 11

update Customer set cid =100 where cid=1;

--tao trigger

create trigger CusUpdate

on Customer instead of update

as

--loai bo khoa ngoai

alter table [Order] nocheck constraint FK_Customer;

--

update Customer set cID = (select cid from Inserted)

where cid = (select cid from Deleted);

update [Order] set cID = (select cid from Inserted)

where cid = (select cid from Deleted);

--dat lai khoa ngoai

alter table [Order] check constraint FK_Customer;

ALTER trigger CusUpdate

on Customer instead of update

as

--loai bo khoa ngoai

--alter table [Order] nocheck constraint FK_Customer;

alter table [order] drop constraint FK_Customer;

--

update Customer set cID = (select cid from Inserted)

where cid = (select cid from Deleted);

update [Order] set cID = (select cid from Inserted)

where cid = (select cid from Deleted);

--dat lai khoa ngoai

alter table [order] add constraint FK_CUstomer foreign key(cid) references Customer;

--alter table [Order] check constraint FK_Customer;

update Customer set cid =12 where cid=123;

SELECT * from Customer;

select * from [Order];

update [order] set cid = 123 where cid = 135;

--tao trigger tang gia san pham len 50%, hien thi gia vua tang

--phuc hoi lai gia ban dau

create trigger trgupdate

on Product

for update

as

select * from Product;

rollback transaction;

update Product set pPrice=pPrice*2;

--tao trigger xoa lan truyen: Neu xoa khach hang thi tat

--cac hoa don lien quan deu phai xoa het

delete from customer where cid = 123;

--tao trigger

create trigger trgdelete_customer

on Customer instead of delete

as

--xoa bang OrderDetail

delete from OrderDetail where

oid in (select oid from [order] where cid in (select cid from Deleted));

--xoa bang Order

delete from [Order] where cid in (select cid from Deleted);

delete from Customer where cid in (select cid from Deleted);

SELECT * from Customer;

select * from [Order];

select * from OrderDetail;