SArroyo Benchmark-SQL Triggers
docx
keyboard_arrow_up
School
Grand Canyon University *
*We aren’t endorsed by this school
Course
400
Subject
Information Systems
Date
Dec 6, 2023
Type
docx
Pages
4
Uploaded by shawnarroyo
Creating
the
table:
sQLQueryl
sql
-
EC...oMRyrdp_user60)*
=
>
[
A
M
DD
~|Create
table
Products
-
(
Product_Code
varchar(1@),
Product_Desc
varchar(1@0),
Product_QOH
INT,
--
Product
Quantity
On
Hand
Product_MIN
INT,
--
Minimum
Quantity
of
the
product
Product_ListPrice
FLOAT,
Product_MinOrder
INT,
--
this
is
the
minimum
quantity
for
restocking
an
order
Product_Reorder
BIT
-|Insert
INTO
Products
'
Values
(1,
'Power
Supplies',
8,
5,
3@.00,
25,
0),
'
(2,
'Hard
Disks',
7,
5,
120.00,
50,
0)
100%
~
@i
Messages
(2
rows
affected)
Completion
time:
Z023-0Z-12TZ1:13:37.3029785+00:00
Data
from
products
table:
SQLQuery2.sql
-
EC...I9MR\rdp_user
(58))*
&
X
e]NIFTINy
IeT-|
I
Jol
[+
¥
|
AAVE:
ORVELT
(1))
Select
*
from
Products;
100%
~
BB
Results
[gi
Messages
Product_Code
Product_Desc
Product_Q0OH
Product_MIN
Product_ListPrice
Product_MinOrder
Product_Reorder
1
i1
|
Power
Supplies
8
5
30
25
0
..........................................
2
2
Hard
Disks
7
5
120
50
0
Create
and
testing
trigger:
SQLQueryd.sql
-
EC...I9MR\rdp_user
(57))*
SQLQuery3.sql
-
EC..I9MR\rdp_user
(53))*
+
X
~ICreate
Trigger
ReOrder
ON
Products
After
Update
AS
-/Begin
=
Update
Products
Set
Product
Reorder
-
1
_
Where
Product
QOH
<
Product
MIN
|
End
100%
-
¥
Messages
Commands
completed
successfully.
Completion
time:
20Z3-02-12TZ1:20:31.4350288+00:00
SQLQueryd.sql
-
EC...I9MR\rdp_user
(57)*
#
X
Qoo
¥Rt
|
I
Tl
I
%
AVe:
TR
GX
)
-|Update
Products
Set
Product
QOH
-
4
'
Where
Product
Code
=
1;
|
Select
*
From
Products
100
%
~
EH
Results
2
Messages
}
1
i1
Power
Supplies
4
5
30
25
.........................................
|
2
2
Hard
Disks
7
5
120
50
SQLQuery2.5ql
-
EC...I9MR\r
Product_Code
Product_Desc
Product_Q0OH
Product_MIN
Product_ListPrice
Product_MinOrder
Product_Reorder
1
0
Part
2-
Creating
audi_log
table:
~ICreate
Table
audi_log(
Product_Code
int,
Previous_Price
int,
New_Price
int,
Date_Price_Modified
date
100
%
¥
Messages
Commands
conpleted successfully.
Completion
time:
Z0Z3-02-12TZ1:54:39.2094760+00:00
Creating
trigger:
—ICreate
Trigger
Price_History
On
Products
AFTER
Update,
Insert
AS
—-1Begin
=]
Insert
INTO
audi_log(
Product
code,
new_price,
date_price_modified)
Select
p.product_code,
d.product
listprice,
i.product
listprice,
GETDATE()
From
products
p
join
inserted
i
on
p.product_code
=
i.product
code
,
join
deleted
d
on
p.product_code
=
d.product_code
End;
00
%
~
E]i
Messages
Commands
completed
successfully.
Completion
time:
Z0Z3-0Z-12ZTZZ:08:04.4939767+00:00
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
Testing:
=lUpdate
Products
Set
product
listprice
=
40
'
Where
product
_code
=
2;
'
Select
*
From
Audi_log
00%
-~
EH
Results
2
Messages
Product_Code
Previous_Price
New_Price
1
i1
30
30
2
2
120
40
Date_Price_Modified
20230212
20230212