32 trang
43 lượt tải
Đáp án trigger | Cơ sở dữ liệu | Trường Đại học Công nghiệp TP.HCM
86
Đáp án trigger môn Cơ sở dữ liệu của Trường Đại học Công nghiệp Thành phố Hồ Chí Minh. Hi vọng tài liệu này sẽ giúp các bạn học tốt, ôn tập hiệu quả, đạt kết quả cao trong các bài thi, bài kiểm tra sắp tới. Mời các bạn cùng tham khảo chi tiết bài viết dưới đây nhé.
Môn: Cơ sở dữ liệu (ĐHCN) 19 tài liệu
Trường: Đại học Công nghiệp Thành phố Hồ Chí Minh 405 tài liệu
Tác giả:
Danh sách Quiz
lOMoARcPSD|40651217
C:\Users\TTTH\AppData\Local\Temp\~vsFA86.sql 1
lOMoARcPSD|40651217
--cau 1=========================================
create table M_Department
(
DepartmentID int not null primary key,
Name nvarchar(50), groupName nvarchar(50)
) table M_Employees
(
EmployeeID int not null primary key,
Firtname nvarchar(50),
MiddleName nvarchar(50),
LastName nvarchar(50),
DepartmentID int foreign key references
M_Department(DepartmentID) )
-
-
t
ạ
o
v
i
e
w
g
o
c
r
e
a
t
e
v
i
e
w
v
i
e
w
_
t
r
i
g
g
e
r
a
s
select EmployeeID, Firtname, MiddleName,
LastName, e.DepartmentID, Name, groupName from
create
lOMoARcPSD|40651217
C:\Users\TTTH\AppData\Local\Temp\~vsFA86.sql 2
M_Employees e join M_Department d on
e.DepartmentID=d.DepartmentID go
---tạo trigger
create
trigger
insteadof_
trigger on
view_trigg
er instead
of insert
as
begin
insert M_Department
select
DepartmentID, Name,
groupName from inserted
insert M_Employees
select EmployeeID,
Firtname, MiddleName, LastName,
DepartmentID from
inserted end
select*from view_trigger
insert view_trigger values(1,
’Nguyen’,’Hoang’,’Huy’,
11,’Marketing’,’Sales’) select*from
M_Department select*from M_Employees
--cau 2=========================================
--Tạo 2 bảng dữ liệu, chŁn dữ liệu lấy
từ cÆc bảng trong AdventureWorks create
table Mcustomer
(
customerID int primary key,
custp
riori
ty
int
)
insert
Mcusto
mer
([Cust
omerID
],cust
priori
ty)
select
[Custo
merID]
, null
from
[Sales
].
[Custo
mer]
where
CustomerID>30100
and
CustomerID<30118
select*from
Mcustomer
lOMoARcPSD|40651217
C:\Users\TTTH\AppData\Local\Temp\~vsFA86.sql 3
create table MsalesOrders
(
SalesOrderID int primary key,
O
r
d
e
r
D
a
t
e
d
a
t
e
,
S
u
b
T
o
t
a
l
m
o
n
e
y
,
customerID int foreign key references
Mcustomer(customerID)
)
insert MsalesOrders
select
[SalesOrderID],OrderDat
e, [SubTotal],
[CustomerID] from
[Sales].
[SalesOrderHeader]
where year([OrderDate])=2008 and
CustomerID>30100 and CustomerID<30118
select*from MsalesOrders
-
-
T
ạ
o
t
r
i
g
lOMoARcPSD|40651217
C:\Users\TTTH\AppData\Local\Temp\~vsFA86.sql 4
g
e
r
g
o
create
trigger
trigger_p
riority
on
MsalesOrd
ers for
insert,
update,
delete as
WITH CTE AS (
select CustomerId from inserted
union
select CustomerId from deleted
)
UPDATE Mcustomer
SET
custpriority =
case
when t.Total <
10000 then 3
when
t.Total between
10000 and 50000
then 2
when
t.Total > 50000
then 1
when t.Total IS NULL then NULL
end
FROM Mcustomer c INNER JOIN CTE ON
CTE.CustomerId = c.CustomerId
LEFT JOIN (
select MsalesOrders.customerID, SUM(SubTotal
Total
from MsalesOrders inner join CTE on
CTE.CustomerId = MsalesOrders.CustomerId
group by MsalesOrders.customerID) t ON
t.CustomerId = c.CustomerId GO
insert Msale values(71847,
’2016-01-01’, 10000, 30112)
select*from Mcustomer
--
cau3====================
========================
====== create table
MDepartment
(
DepartmentID int not null primary key,
Name nvarchar(50),
NumOfEmployee int
lOMoARcPSD|40651217
C:\Users\TTTH\AppData\Local\Temp\~vsFA86.sql 5
)
t
a
b
l
e
M
e
m
p
l
o
y
e
e
s
(
EmployeeID int not null,
Firtname nvarchar(50),
MiddleName nvarchar(50),
LastName nvarchar(50),
DepartmentID int foreign key
references MDepartment(DepartmentID)
constraint pk_emp_depart primary
key(EmployeeID, DepartmentID)
)
s
e
l
e
c
t
*
f
r
o
m
[
d
b
o
]
.
[
M
e
m
p
l
o
y
e
e
s
]
create
lOMoARcPSD|40651217
C:\Users\TTTH\AppData\Local\Temp\~vsFA86.sql 6
g
o
insert Memployees (EmployeeID, Firtname,
MiddleName, LastName, DepartmentID)
select p.BusinessEntityID, FirstName,
MiddleName, LastName, DepartmentID from
HumanResources.EmployeeDepartmentHistory
e join Person.Person p on e.
B
usin
essE
ntit
yID=
p.Bu
sine
ssEn
tity
ID
go
in
se
rt
MD
ep
ar
tm
en
t
(D
ep
ar
tm
en
tI
D,
Na
me
)
se
le
ct
De
pa
rt
me
nt
ID
,
Na
me
fr
om
Hu
ma
nR
es
ou
rc
es
.D
ep
ar
tm
lOMoARcPSD|40651217
C:\Users\TTTH\AppData\Local\Temp\~vsFA86.sql 7
en
t
go
crea
te
trig
ger
cau3
on
[dbo
].
[Mem
ploy
ees]
for
inse
rt
as
declare
@numofEmp int,
@DepartID int
select
@DepartID=i.De
partmentID
from inserted
i set
@numofEmp=(sel
ect COUNT(*)
from [dbo].
[Memployees] e
where e.DepartmentID=@DepartID
)
if @numofEmp>200
begin
print ’so nhan vien da du’
rollback
end
else
u
p
d
a
t
e
M
D
e
p
a
r
t
m
e
n
t
lOMoARcPSD|40651217
C:\Users\TTTH\AppData\Local\Temp\~vsFA86.sql 8
s
e
t
N
u
m
O
f
E
m
p
l
o
y
e
e
=
@
n
u
m
o
f
E
m
p
w
h
e
r
e
D
e
p
a
r
t
m
e
n
t
I
D
=
@
D
e
p
a
r
t
I
D
lOMoARcPSD|40651217
C:\Users\TTTH\AppData\Local\Temp\~vsFA86.sql 9
g
o
-
-
t
e
s
t
insert [dbo].[Memployees] values(291,
’Nguyen’,’Hoang’,’Anh’,1)
--kiem tra ket qua
selec
t
[Depa
rtmen
tID],
count
([Emp
loyee
ID])
from
[dbo]
.
[Memp
loyee
s]
group
by
[Depa
rtmen
tID]
--
cau4===============
===================
================
select*from
[Purchasing].
[Vendor]
select*from
[Purchasing].
[PurchaseOrderHeade
r] go
CREATE TRIGGER Purchasing.LowCredit ON
Purchasing.PurchaseOrderHeader
AFTER INSERT
AS
IF EXISTS (SELECT *
FROM Purchasing.PurchaseOrderHeader AS
p
JOIN inserted AS i
ON p.PurchaseOrderID =
i.PurchaseOrderID
JOIN Purchasing.Vendor AS v
ON v.BusinessEntityID = p.VendorID
WHERE v.CreditRating = 5
)
BEGIN
lOMoARcPSD|40651217
C:\Users\TTTH\AppData\Local\Temp\~vsFA86.sql 10
RAISERROR (’A vendor’’s credit
rating is too low to accept new
purchase orders.’, 16, 1);
ROLLBACK TRANSACTION;
RETURN
END;
GO
--
cau5=============================================
=====
--Viết một trigger thực hiện trŒn bảng
SalesOrderDetail.
--Khi chŁn thŒm một ơn ặt h ng v o bảng
SalesOrderDetail
--với số lượng xÆc ịnh trong field OrderQty, nếu
số lượng trong kho
--(ProductInventory lưu th ng tin số lượng sản
phẩm trong kho) Quantity> OrderQty
--th cập nhật lại số lượng trong kho
--Quantity= Quantity - OrderQty,
--ngược lại nếu Quantity = 0 th xuất th ng bÆo
Kho hết h ng
-
-
v
ồ
n
g
t
h
ờ
i
h
ủ
y
g
i
a
o
t
Æ
c
-
-
-
/
/
t
a
o
b
a
n
n
g
lOMoARcPSD|40651217
C:\Users\TTTH\AppData\Local\Temp\~vsFA86.sql 11
M
S
a
l
e
s
O
r
d
e
r
D
e
t
a
i
l
c
r
e
a
t
e
t
a
b
l
e
M
S
a
l
e
s
O
r
d
e
r
D
e
t
a
i
l
(
SalesOrderID int not null ,
SalesOrderDetailID int not null primary key,
ProductID int not null ,
OrderQty smallint
)
alter table MSalesOrderDetail
add constraint fk_saleOrderid foreign key
(SalesOrderID) references MSalesOrderHeader
(SalesOrderID), constraint
fk_productID foreign key(ProductID)
references MProduct(MProductID)
lOMoARcPSD|40651217
C:\Users\TTTH\AppData\Local\Temp\~vsFA86.sql 12
insert
MSalesOrderDetail(SalesOrderID,SalesOrder
DetailID, ProductID,OrderQty)
select[SalesOrderID],
[SalesOrderDetailID],[ProductID],
[OrderQty] from [Sales].
[SalesOrderDetail]
where [ProductID] in(select MProductID from
MProduct)
-
-
/
/
t
ạ
o
b
ả
n
g
M
P
r
o
d
u
c
t
_
i
n
v
e
n
t
o
r
y
c
r
e
a
t
e
t
a
b
l
e
M
P
r
o
d
u
c
t
_
lOMoARcPSD|40651217
C:\Users\TTTH\AppData\Local\Temp\~vsFA86.sql 13
i
n
v
e
n
t
o
r
y
( prod
uc
tI
D
in
t
no
t
nu
ll
pr
im
ar
y
ke
y,
qu
an
ti
ty
sm
al
li
nt
)
insert MProduct_inventory
select
[ProductID],s
um([Quantity]
) as
sumofquatity
from
[Production].
[ProductInven
tory]
group by
[ProductID]
go
----tao trigger
al
te
r
tr
ig
ge
r
ca
u5
on
MS
al
lOMoARcPSD|40651217
C:\Users\TTTH\AppData\Local\Temp\~vsFA86.sql 14
es
Or
de
rD
et
ai
l
fo
r
in
se
rt
as
begin
declare
@sldathang int, @sltrongkho
int, @masp int
select @masp =i.ProductID
from inserted i
select
@sldathang=i.OrderQty from
inserted i set
@sltrongkho=(select
quantity from
MProduct_inventory
where productID=@masp)
if
@sldath
ang<@sl
trongkh
o
update
MProduc
t_inven
tory
set
quantit
y=quant
ity-
@sldath
ang
where ProductID=@masp
else
begin
print ’Het hang’
rollback tran
end
end
-
-
-
t
h
u
c
t
h
i
lOMoARcPSD|40651217
C:\Users\TTTH\AppData\Local\Temp\~vsFA86.sql 15
t
r
i
g
g
e
r
i
n
s
e
r
t
[
d
b
o
]
.
[
M
S
a
l
e
s
O
r
d
e
r
D
e
t
a
i
l
]
v
a
l
u
e
s
(
7
4
1
4
2
,
1
1
9
0
9
7
lOMoARcPSD|40651217
C:\Users\TTTH\AppData\Local\Temp\~vsFA86.sql 16
,
7
8
7
,
2
0
)
--
cau6=====================
=========================
==== create table
M_SalesPerson
(
SalePSID int not null primary key,
TerritoryID int,
BonusPS money
) table M_SalesOrderHeader
(
SalesOrdID int not null primary key,
OrderDate date,
SubTotalOrd money,
SalePSID int foreign key references
M_SalesPerson(SalePSID)
)
go
CREATE trigger
bonus_emp on
[dbo].
[M_SalesOrderHead
er] for insert as
begin
declare @tt
float, @spersonID
int
select
@spersonID=
i.SalePSID from
inserted i
set @tt=(select
sum([SubTotalOrd]
)
from
[dbo].
[M_SalesOrderHead
er]
where
SalePSID=@sperson
ID) if
@tt>10000000
begin
update
[dbo].
[M_Sale
create
lOMoARcPSD|40651217
C:\Users\TTTH\AppData\Local\Temp\~vsFA86.sql 17
sPerson
]
set
BonusPS
=BonusP
S*1.5
w
h
e
r
e
S
a
l
e
P
S
I
D
=
@
s
p
e
r
s
o
n
I
D
e
n
d
e
n
d
--==========
alter trigger bonus_emp on [dbo].
[M_SalesOrderHeader]
f
o
r
i
n
s
e
r
t
lOMoARcPSD|40651217
C:\Users\TTTH\AppData\Local\Temp\~vsFA86.sql 18
a
s
begin
declare @tt float, @spersonID int
select @spersonID= i.SalePSID from
inserted i set @tt=(select sum(h.
[SubTotalOrd]) from
[dbo].[M_SalesOrderHeader] h join inserted i on
h.SalesOrdID=i.
SalesOrdID
where
h.SalePSID
=@spersonI
D)
if
@tt>100000
00
update
[dbo].
[M_Sale
sPerson
]
set
BonusPS
=BonusP
S*1.5
where SalePSID=@spersonID
end
s
e
l
e
c
t
[
S
a
l
e
P
S
I
D
]
,
s
u
m
(
[
S
u
b
T
o
lOMoARcPSD|40651217
C:\Users\TTTH\AppData\Local\Temp\~vsFA86.sql 19
t
a
l
O
r
d
]
)
f
r
o
m
[
d
b
o
]
.
[
M
_
S
a
l
e
s
O
r
d
e
r
H
e
a
d
e
r
]
g
r
o
u
p
b
y
[
S
a
l
e
P
S
I
D
]
lOMoARcPSD|40651217
C:\Users\TTTH\AppData\Local\Temp\~vsFA86.sql 20
o
r
d
e
r
b
y
s
u
m
(
[
S
u
b
T
o
t
a
l
O
r
d
]
)
d
e
s
c
select
*from
[dbo].
[M_Sale
sPerson
]
select*
from
[dbo].
[M_Sale
sOrderH
eader]
insert
[dbo].
[M_Sale
sOrderH
eader]
values(
71958,
’2016-
10-
10’,900
0, 277)
---------
create table M_Department
(
DepartmentID int not null primary key,
Name nvarchar(50), groupName nvarchar(50)
Bấm Tải xuống để xem toàn bộ.