· 5 years ago · Feb 14, 2020, 04:48 PM
1SELECT * FROM SYS.TRIGGERS
2-----------------------------------------------------------------------------------
3--------------
4/*****1. CREATE TRIGGER (AFTER)***/
5IF OBJECT_ID('Vendors_INSERT') is not null
6DROP TRIGGER Vendors_INSERT
7GO
8CREATE TRIGGER Vendors_INSERT
9 ON Vendors
10 AFTER INSERT
11AS
12 UPDATE Vendors
13 SET VendorState = UPPER(VendorState)
14 WHERE VendorID IN (SELECT VendorID FROM Inserted);
15 Select * from Inserted
16--1. CREATE THE TRIGGER FIRST AND MAKE SURE IT EXISTS IN YOUR DATABASE
17
18--2. TRY TO INSERT WITH MIXED-CASE STATE ERROR 'Oh' instead of 'OH'
19INSERT Vendors VALUES ('Peerless Uniforms, Inc.', '785 S Pixley
20Rd', NULL, 'Piqua', 'OH', '45356', '(937) 555-8845',NULL, NULL,
214, 550);
22--3. CHECK TO SEE IF STATE HAS BEEN CORRECTED OR NOT
23SELECT * FROM Vendors
24Where VendorName LIKE 'Peerless Uniforms%'
25--4. What data is in the Inserted table? Write the row of data here.
26125 Peerless Uniforms Inc., 785 S Pixley Rd, NULL, Piqua, OH, 45356, (937) 555-8845 ,NULL, NULL, 4, 550
27-----------------------------------------------------------------------------------
28---------------
29/*****2. CREATE TRIGGER (AFTER)***/
30SELECT *
31INTO VendorCopy
32FROM Vendors
33SELECT *
34INTO InvoiceCopy
35FROM Invoices
36--1. CREATE Vendor_Copy & Invoice_Copy
37IF OBJECT_ID('VendorCopy_DELETE_RI') is not null
38DROP TRIGGER VendorCopy_DELETE_RI
39GO
40CREATE TRIGGER VendorCopy_DELETE_RI
41 ON VendorCopy
42 AFTER DELETE
43
44AS
45 IF EXISTS (SELECT * FROM Deleted JOIN InvoiceCopy
46 ON Deleted.VendorID = InvoiceCopy.VendorID)
47 Select * from Deleted
48 BEGIN;
49 THROW 50002, 'VendorID in use.', 1;
50 ROLLBACK TRAN;
51 END;
52
53
54
55
56
57
58
59--2. CREATE THE TRIGGER FIRST AND MAKE SURE IT EXISTS IN YOUR DATABASE
60--3. TRY TO DELETE AND THE TRIGGER SHOULD FIRE AND THROW AN ERROR
61DELETE VendorCopy
62WHERE VendorID = 34
63--4. Why does it throw an error?
64--Because its used elsewhere so its not deleted
65--5. What data is in the Deleted table? Write it here.
66-- 34 IBM PO Box 61000 NULL SanFrancisco CA 94161 (800)555-4426 Cameron Trentin 1 160
67--6. What happens if you try to delete the same vendor from the Vendors table?
68DELETE Vendors
69WHERE VendorID = 34
70--It puts an error that the delete conflcited with the foreign key
71
72--7. Is there a need to write a Trigger for Referential Integrity for the Vendors table? Why not?
73--No because the server does automatically now
74-----------------------------------------------------------------------------------
75-------------------
76/****CREATE TRIGGER (INSTEAD OF) ****/
77GO
78CREATE VIEW IBM_Invoices AS
79SELECT InvoiceNumber, InvoiceDate, InvoiceTotal
80from Invoices where VendorID = (SELECT VendorID FROM Vendors WHERE VendorName
81= 'IBM')
82GO
83SELECT * FROM IBM_Invoices
84--1. CREATE THE VIEW AND TEST IT
85--2. TRY TO INSERT AND THERE SHOULD BE AN ERROR
86INSERT IBM_Invoices
87VALUES ('RA23988', '2012-05-09', 417.34);
88--3. Why is there an error?
89-- Table requires more informations than is given
90IF OBJECT_ID('IBM_Invoices_INSERT') is not null
91 DROP TRIGGER IBM_Invoices_INSERT
92GO
93CREATE TRIGGER IBM_Invoices_INSERT
94 ON IBM_Invoices
95 INSTEAD OF INSERT
96AS
97DECLARE @InvoiceDate smalldatetime,
98 @InvoiceNumber varchar(50),
99 @InvoiceTotal money,
100 @VendorID int,
101 @InvoiceDueDate smalldatetime,
102 @TermsID int,
103 @DefaultTerms smallint,
104 @TestRowCount int
105
106SET @TestRowCount = (SELECT COUNT(*) FROM Inserted)
107
108IF @TestRowCount = 1
109
110 BEGIN
111 SET @InvoiceNumber = (SELECT InvoiceNumber FROM Inserted)
112
113 SET @InvoiceDate = (SELECT InvoiceDate FROM Inserted)
114
115 SET @InvoiceTotal = (SELECT InvoiceTotal FROM Inserted)
116
117 IF (@InvoiceDate IS NOT NULL AND @InvoiceNumber IS NOT NULL AND @InvoiceTotal IS NOT NULL)
118
119 BEGIN
120 SET @VendorID = (SELECT VendorID FROM Vendors
121 WHERE VendorName = 'IBM')
122
123 SET @TermsID = (SELECT DefaultTermsID FROM Vendors
124 WHERE VendorName = 'IBM')
125
126 SET @DefaultTerms = (SELECT TermsDueDays FROM Terms
127 WHERE TermsID = @TermsID)
128
129 SET @InvoiceDueDate = @InvoiceDate + @DefaultTerms
130
131 INSERT Invoices
132 (VendorID, InvoiceNumber, InvoiceDate,
133 InvoiceTotal, TermsID, InvoiceDueDate,
134 PaymentDate)
135 VALUES (@VendorID, @InvoiceNumber,
136 @InvoiceDate, @InvoiceTotal,
137 @TermsID, @InvoiceDueDate, NULL)
138 END
139 END
140ELSE
141 THROW 50027, 'Limit INSERT to a single row.', 1
142
143--4. CREATE THE TRIGGER THAT WILL INSERT THROUGH THE UPDATABLE VIEW AND MAKE SURE IT EXISTS IN YOUR DATABASE
144
145
146--5. TRY TO INSERT AGAIN
147INSERT IBM_Invoices
148VALUES ('RA23988', '2012-05-09', 417.34);
149
150--6. CHECK TO SEE IF THE DATA HAS BEEN INSERTED OR NOT
151select * from IBM_Invoices
152select * from Invoices where VendorID = 34
153
154--7. What is the main difference between AFTER and INSTEAD OF triggers?
155-- After triggers fix errors after the insert/update/delete statement has been completed and instead of triggers
156-- fix the errors before the insert/update/delete statement completes
157
158
159
160
161
162Annotations