1 / 60

Simple Programming Part 2 SECTION 8

Simple Programming Part 2 SECTION 8. CURSORS and TRIGGERS. Cursors. An extremely important programming construct . . Stands for: “Current set of records” It allows you to take a subset of data and output the information in various ways. CURSOR Commands: The OPEN command

cpinegar
Download Presentation

Simple Programming Part 2 SECTION 8

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Simple Programming Part 2SECTION 8 CURSORS and TRIGGERS

  2. Cursors • An extremely important programming construct . • Stands for: • “Current set of records” • It allows you to take a subset of data and output the information in various ways.

  3. CURSOR Commands: • The OPEN command • Opens the cursor for use • The FETCH command • Retrieves a specific row from a cursor • The CLOSE command • Closes an open cursor • The DEALLOCATE command • Removes the cursor

  4. Cursor Declaration • Declared in the following manner: • Declare variables to hold the output from the cursor. • Declare the cursor object. • Assign the query to the cursor. • Open the cursor and fetch the first row • Loop until there are no more results. In the loop print out the Customer Number and the Customer Last Name from the result set and fetch the net • Close the cursor. • Deallocate the cursor to free up any memory or open result sets.

  5. The CUSTOMER Table

  6. The following uses a CURSOR from the CUSTOMER table • Declare variables to hold the output from the cursor. DECLARE @CustomerNumber as numeric; DECLARE @CustomerName as nvarchar(20); • Declare the cursor object. DECLARE @customer_cursor as CURSOR; • Assign the query to the cursor. SET @customer_cursor = CURSOR FOR SELECT CustomerNUmber, CustomerName FROM CUSTOMER; • Open the cursor and fetch the fiurst row OPEN @customer_cursor; FETCH NEXT FROM @customer_cursor INTO @CustomerNumber, @CustomerName; • Loop until there are no more results. In the loop print out the Customer Number and the Customer Last Name from the result set and fetch the net row WHILE @@FETCH_STATUS = 0 BEGIN PRINT cast(@CustomerNumber as VARCHAR (50)) + ' ' + @CustomerName; FETCH NEXT FROM @customer_cursor INTO @CustomerNumber, @CustomerName; END • Close the cursor. CLOSE @customer_cursor; • Deallocate the cursor to free up any memory or open result sets. DEALLOCATE @customer_cursor;

  7. What is in the cursor?

  8. The following uses CONCAT using a CURSOR for CUSTOMER DECLARE @Customer as nvarchar(30); DECLARE @customer_cursor as CURSOR; SET @customer_cursor = CURSOR FOR SELECT CONCAT(CustomerNumber,’ - ‘, CustomerName) FROM CUSTOMER; OPEN @customer_cursor; FETCH NEXT FROM @customer_cursor INTO @Customer WHILE @@FETCH_STATUS = 0 BEGIN PRINT @Customer FETCH NEXT FROM @customer_cursor INTO @Customer END CLOSE @customer_cursor; DEALLOCATE @customer_cursor;

  9. Notice the dash mark ‘ - ‘ Notice the dash mark ‘ - ‘

  10. The Chinese Zodiac Procedure using a cursor The CHINESE_ZODIAC table will be the basis of the cursor, zodiac_cursor

  11. CREATE PROCEDURE Chinese_Zodiac_Proc05 (@birthyear NUMERIC) --This procedure deals with birth years and the Chinese Zodiac AS DECLARE @BD NUMERIC(5), @X NUMERIC(10,6), @Y NUMERIC(4), @Z NUMERIC(2), @Number NUMERIC = 0, @ZodiacSign nVARCHAR(10), @zodiac_cursor as CURSOR SET @zodiac_cursor = CURSOR FOR select * from chinese_zodiac BEGIN SET @BD = @birthyear --The following deals with BCE years. There is no year 0 IF @birthyear = 0 BEGIN SET @birthyear = 1 PRINT 'Year 0 is not a vaild year. It should be year 1.' END IF @BD <1 SET @BD = @BD +1 SET @X = @BD/12 SET @Y = @BD/12 --The following reduces @Y by 1 when SQL Server rounds up a number to the next highest number. IF @Y > @X SET @Y = @Y-1 SET @Z = (@X - @Y)*12 END OPEN @zodiac_cursor FETCH NEXT FROM @zodiac_cursor INTO @Number, @ZodiacSign WHILE @@FETCH_STATUS = 0 and @Z <> @Number FETCH NEXT FROM @zodiac_cursor INTO @Number,@ZodiacSign PRINT CAST(@birthyear as nVARCHAR(20)) + ' ' + @ZodiacSign PRINT '*****' CLOSE @zodiac_cursor DEALLOCATE @zodiac_cursor

  12. WHERE CURRENT OF • Syntax: WHERE CURRENT OF cursor_name;

  13. Want to change Customer Number 5155 to 5156

  14. WHERE CURRENT OF: When you use SQL you can update the current row by using an UPDATE statement that includes a WHERE CURRENT OF clause. Changes made with this clause affect only the row on which the cursor is positioned. When a cursor is based on a join, only the table_name specified in the UPDATE statement is modified. Other tables participating in the cursor are not affected.

  15. The previous example only changed on line of the table • Where CustomerNumber = 5155 • To change multiple rows we need to loop through the table and change each row, one at a time. • Need to use @@FETCH_STATUS • Returns the status of the last cursor FETCH statement issued against any cursor currently opened • Also use a loop with BEGIN …FETCH….END • WHILE @@FETCH_STATUS = 0 • This means that while the fetch statement is successful • Then do the next line in the procedure

  16. Change multiple rows:

  17. MORE COMPLEX PROCEDURE CREATE TABLE AlphaLtd_purchase (ProductName nVARCHAR(25), Salesperson nVARCHAR(3), PurchaseDate DATETIME2, Quantity NUMERIC(4,2)) CREATE TABLE AlphaLtd_product (ProductName nVARCHAR(25) PRIMARY KEY, ProductPrice NUMERIC(4,2), QuantityOnHand NUMERIC(5,0), LastStockDate DATETIME2) INSERT INTO AlphaLtd_product VALUES ('Small Widget',99,1,'2014-01-15'); INSERT INTO AlphaLtd_product VALUES ('Medium Wodget',75,1000,'2013-01-15'); INSERT INTO AlphaLtd_product VALUES ('Chrome Phoobar',50,100,'2014-01-15'); INSERT INTO AlphaLtd_product VALUES ('Round Chrome Snaphoo',25,10000, null); INSERT INTO AlphaLtd_product VALUES ('Extra Huge Mega Phoobar +',9.95,1234,'2015-01-15'); INSERT INTO AlphaLtd_product VALUES ('Square Zinculator',45,1,'2013-12-31'); INSERT INTO AlphaLtd_product VALUES ('Anodized Framifier',49,5, NULL); INSERT INTO AlphaLtd_product VALUES ('Red Snaphoo',1.95,10,'2012-12-31'); INSERT INTO AlphaLtd_product VALUES ('Blue Snaphoo',1.95,10,'2012-12-30'); INSERT INTO AlphaLtd_purchase VALUES ('Small Widget','CA','2014-07-14',1); INSERT INTO AlphaLtd_purchase VALUES ('Medium Wodget','BB','2014-07-14',75); INSERT INTO AlphaLtd_purchase VALUES ('Chrome Phoobar','GA','2014-07-14',2); INSERT INTO AlphaLtd_purchase VALUES ('Small Widget','GA','2014-07-15',8); INSERT INTO AlphaLtd_purchase VALUES ('Medium Wodget','LB','2014-07-15',20); INSERT INTO AlphaLtd_purchase VALUES ('Round Chrome Snaphoo','CA','2014-07-16',5); INSERT INTO AlphaLtd_purchase VALUES ('Small Widget','CA','2014-07-17',1); THE CURSOR SELECT pur.Salesperson, pur.Quantity, prod.ProductPrice FROM AlphaLtd_purchase pur, AlphaLtd_product prod WHERE pur.ProductName = prod.ProductName ORDER BY Salesperson

  18. DECLARE @NewSalesperson nVARCHAR(3),@OldSalesperson nVARCHAR(3)='xxx', @QuantitySold NUMERIC(3),@ProductPrice NUMERIC(4),@TotalSale NUMERIC(6)=0,@Rate NUMERIC(3)=2 DECLARE @commission_cur as CURSOR SET @commission_cur = CURSOR FOR SELECT pur.Salesperson, pur.Quantity, prod.ProductPrice FROM AlphaLtd_purchase pur, AlphaLtd_product prod WHERE pur.ProductName=prod.ProductName ORDER BY Salesperson OPEN @commission_cur FETCH NEXT FROM @commission_cur INTO @NewSalesperson,@QuantitySold,@ProductPrice PRINT '*' WHILE @@FETCH_STATUS = 0 BEGIN IF @OldSalesperson = 'xxx' --This statement only deals with the first record when @oldSalesperson = 'xxx' BEGIN SET @TotalSale = @TotalSale + (@QuantitySold * @ProductPrice) SET @oldSalesperson = @NewSalesperson FETCH NEXT FROM @commission_cur INTO @NewSalesperson,@QuantitySold,@ProductPrice END IF @OldSalesperson = @NewSalesperson --This ststement deals with the second record onwards when @OldSalesperson = @NewSalesperson. --Total sales are accumulated for each salesperson. BEGIN SET @TotalSale = @TotalSale + (@QuantitySold * @ProductPrice) SET @oldSalesperson = @NewSalesperson FETCH NEXT FROM @commission_cur INTO @NewSalesperson,@QuantitySold,@ProductPrice END IF @OldSalesperson <> @NewSalesperson --This statement handles the flow when a different salesperson is encountered. --i.e. @OldSalesperson <> @NewSalesperson BEGIN --The following prints out the total sale for the previous salesperson. --CHAR(13) is a carriage return and CHAR(9) is an indent PRINT 'Salesperson: ' + @OldSalesperson + CHAR(13) + CHAR(9) --the next line will have a carriage return and be indented + 'Total Quantity Sold: ' + CONVERT(nVARCHAR(20),@TotalSale)+' units'+CHAR(13)+CHAR(9) + 'Commission - $' + CONVERT(nVARCHAR(20),CAST(@TotalSale*@Rate/100 as MONEY),1) PRINT '***' SET @TotalSale = 0 SET @TotalSale = @TotalSale + (@QuantitySold * @ProductPrice) SET @oldSalesperson = @NewSalesperson FETCH NEXT FROM @commission_cur INTO @NewSalesperson,@QuantitySold,@ProductPrice END END --The while loop has now exited since the last record has already been fetched. --The following prints out the commission for the last salesperson PRINT 'Salesperson: ' + @OldSalesperson + CHAR(13) + CHAR(9) + 'Total Quantity Sold: ' + CONVERT(nVARCHAR(20),@TotalSale)+' units'+CHAR(13)+CHAR(9) + 'Commission - $' + CONVERT(nVARCHAR(20),CAST(@TotalSale*@Rate/100 as MONEY),1) PRINT '*' CLOSE commission_cur DEALLOCATE commission_cur

  19. Note the comment lines. They help to follow the logic.

  20. The SELECT statement • The FETCH commands • The IF statements

  21. Notice the carriage returns and indents

  22. Cursor in Cursor The problem: What are the courses, and the student number and names of the students who took courses in semester 1 of 2008? The solution should look like the following.

  23. The tables – used in SQL section CREATE TABLE U_SECTIONS( SectionId NUMERIC(5), CourseNum NUMERIC(4) CONSTRAINT CourseNum_fk REFERENCES U_COURSES, SectionNum NUMERIC(2), SemesterNum NUMERIC(6) CONSTRAINT SemesterNum_fk REFERENCES U_SEMESTER, FacNum NUMERIC(5) CONSTRAINT FacNum_fk REFERENCES U_FACULTY, CourseSecDay nVARCHAR(5), CourseSecTime nVARCHAR(8), LocNum NUMERIC(2) CONSTRAINT LocNum2_fk REFERENCES U_CLASS_LOCATION, CONSTRAINT SectionId_pk PRIMARY KEY (SectionId)); CREATE TABLE U_COURSES( CourseNum NUMERIC(4), CourseName nVARCHAR(8), CourseDesc nVARCHAR(40), CONSTRAINT CourseNum_pk PRIMARY KEY (CourseNum)); INSERT INTO U_COURSES VALUES (1001, 'Actg1B10', 'Intro. to Accounting'); INSERT INTO U_COURSES VALUES (1002, 'Fnce2G56', 'Intro. to Finance'); INSERT INTO U_COURSES VALUES (1003, 'Actg2D17', 'Intermediate Accounting'); INSERT INTO U_COURSES VALUES (1004, 'Actg4R43', 'Advanced Accounting'); INSERT INTO U_COURSES VALUES (1005, 'Mktg2D24', 'Intro. to Marketing'); INSERT INTO U_SECTIONS VALUES (10000,1001, 1, 200601, 3001, 'MWF', '10:00 AM', 35); INSERT INTO U_SECTIONS VALUES (10001,1001, 2, 200601, 3001, 'MWF', '3:00 PM', 35); INSERT INTO U_SECTIONS VALUES (10002,1002, 2, 200601, 3003, 'MTWRF', '11:00 AM', 55); INSERT INTO U_SECTIONS VALUES (10003,1003, 1,200702, 3004, 'MW', '9:00 AM', 45); INSERT INTO U_SECTIONS VALUES (10004,1003, 2, 200702, 3004, 'MW', '11:00 PM', 30); INSERT INTO U_SECTIONS VALUES (10005,1005, 1, 200702, 3006, 'T', '1:00 PM', 10); INSERT INTO U_SECTIONS VALUES (10006,1002, 1, 200703, 3002, 'W', '9:00 AM', 25); INSERT INTO U_SECTIONS VALUES (10007,1002, 2, 200703, 3002, 'R', '9:00 PM', 25); INSERT INTO U_SECTIONS VALUES (10008,1004, 1, 200703, 3005, 'MWF', '1:00 PM', 55); INSERT INTO U_SECTIONS VALUES (10009,1001, 1, 200701, 3001, 'MWF', '3:00 PM', 20); INSERT INTO U_SECTIONS VALUES (10010,1002, 1, 200701, 3006, 'MW', '3:00 PM', 40); INSERT INTO U_SECTIONS VALUES (10011,1002, 2, 200701, 3006, 'MW', '5:00 PM', 40); INSERT INTO U_SECTIONS VALUES (10012,1003, 1, 200801, 3003, 'W', '8:00 AM', 30); INSERT INTO U_SECTIONS VALUES (10013,1003, 2, 200801, 3003, 'W', '11:00 AM', 40); INSERT INTO U_SECTIONS VALUES (10014,1004, 1, 200801, 3005, 'MTWRF, '5:00 PM', 20);

  24. The tables - continued CREATE TABLE U_ENROLLMENT( StuNum NUMERIC(7) CONSTRAINT StuNum_fk REFERENCES U_STUDENT, SectionId NUMERIC(5) CONSTRAINT CourseNum2_fk REFERENCES U_SECTIONS, Mark NUMERIC(2), Grade nVARCHAR(1), CONSTRAINT StuSection_pk PRIMARY KEY (StuNum, SectionId)); CREATE TABLE U_STUDENT( StuNum NUMERIC(7), StuLast nVARCHAR(20), StuFirst nVARCHAR(10), StuAddress nVARCHAR(40), StuCity nVARCHAR(20), StuProv nVARCHAR(3), StuPostCode nVARCHAR(6), StuYear nVARCHAR(2), CONSTRAINT StuNum_pk PRIMARY KEY (StuNum)); INSERT INTO U_ENROLLMENT VALUES (2074567, 10000, 80, 'A'); INSERT INTO U_ENROLLMENT VALUES (2061032, 10001, 75, 'B'); INSERT INTO U_ENROLLMENT VALUES (2075947, 10002, 85, 'A'); INSERT INTO U_ENROLLMENT VALUES (2059152, 10003, 70, 'B'); INSERT INTO U_ENROLLMENT VALUES (2057612, 10004, 90, 'A'); INSERT INTO U_ENROLLMENT VALUES (2061032, 10004, 78, 'B'); INSERT INTO U_ENROLLMENT VALUES (2075947, 10005, 68, 'C'); INSERT INTO U_ENROLLMENT VALUES (2062109, 10005, 88, 'A'); INSERT INTO U_ENROLLMENT VALUES (2057612, 10006, 75, 'B'); INSERT INTO U_ENROLLMENT VALUES (2087194, 10007,0,NULL); INSERT INTO U_ENROLLMENT VALUES (2080034, 10007,0,NULL); INSERT INTO U_ENROLLMENT VALUES (2059152, 10008,0,NULL); INSERT INTO U_ENROLLMENT VALUES (2061032, 10009,0,NULL); INSERT INTO U_ENROLLMENT VALUES (2057612, 10010, 78, 'B'); INSERT INTO U_ENROLLMENT VALUES (2074567, 10011, 85, 'A'); INSERT INTO U_ENROLLMENT VALUES (2075947, 10012, 80, 'A'); INSERT INTO U_ENROLLMENT VALUES (2061032, 10013,0,NULL); INSERT INTO U_ENROLLMENT VALUES (2057612, 10013,0,NULL); INSERT INTO U_ENROLLMENT VALUES (2075947, 10014,0,NULL); INSERT INTO U_ENROLLMENT VALUES (2057612, 10014,0,NULL); INSERT INTO U_STUDENT VALUES (2074567, 'Randolph', 'Jennifer', '156 Wendover Drive', 'Toronto', 'ONT', 'M3A1H6', 'SO'); INSERT INTO U_STUDENT VALUES (2057612, 'Jameson', 'Philip', '812 Augustus Blvd.', 'Hamilton', 'ONT', 'L9B3S2', 'SR'); INSERT INTO U_STUDENT VALUES (2087194, 'Smithers', 'Paula', '7135 Yonge Street', 'Toronto', 'ONT', 'M6H2T4', 'FR'); INSERT INTO U_STUDENT VALUES (2062109, 'Gordon', 'Emily', '812 Augustus Blvd.', 'St. Catharines', 'ONT', 'L2S7D3', 'JR'); INSERT INTO U_STUDENT VALUES (2059152, 'Anderson', 'Cynthia', '89 Ball Road', 'Toronto', 'ONT', 'M1J8R4', 'SR'); INSERT INTO U_STUDENT VALUES (2061032, 'VanDyk', 'William', '452 Overlord Place', 'St. Catharines', 'ONT', 'L2S2K7', 'JR'); INSERT INTO U_STUDENT VALUES (2075947, 'Brotherson', 'Stephanie', '5134 Main Street', 'Hamilton', 'ONT', 'L8T6Y2', 'SO'); INSERT INTO U_STUDENT VALUES (2080034, 'Collins', 'Amanda', '645 Powerline Road', 'St. Catharines', 'ONT', 'L2S4R2', 'FR');

  25. The First Cursor SELECT US.SectionId, UC.CourseName, US.SectionNum FROM U_COURSES UC, U_SECTIONS US WHERE UC.CourseNum = US.CourseNum AND US.SemesterNum = 200801 ORDER BY US.SectionId, UC.CourseName, US.SectionNum; The Second Cursor SELECT UE.SectionId, UE.StuNum, UST.StuFirst, UST.StuLast FROM U_ENROLLMENT UE, U_STUDENT UST, U_SECTIONS US WHERE UE.StuNum = UST.StuNum AND UE.SectionId = US.SectionId AND US.SemesterNum = 200801

  26. CREATE PROCEDURE NewUniversity_proc AS DECLARE @x NUMERIC(5), @y nVARCHAR(8), @z NUMERIC(1), @a NUMERIC(5), @b NUMERIC(7), @c nVARCHAR(10), @d nVARCHAR(20); DECLARE @first_cur as CURSOR; SET @first_cur = CURSOR FOR SELECT US.SectionId, UC.CourseName, US.SectionNum FROM U_COURSES UC, U_SECTIONS US WHERE UC.CourseNum = US.CourseNum AND US.SemesterNum = 200801 ORDER BY US.SectionId, UC.CourseName, US.SectionNum; OPEN @first_cur; FETCH NEXT FROM @first_cur INTO @x, @y, @z; WHILE @@FETCH_STATUS = 0 -- Outer cursor loop BEGIN DECLARE @second_cur as CURSOR; SET @second_cur = CURSOR FOR SELECT UE.SectionId, UE.StuNum, UST.StuFirst, UST.StuLast FROM U_ENROLLMENT UE, U_STUDENT UST, U_SECTIONS US WHERE UE.StuNum = UST.StuNum AND UE.SectionId = US.SectionId AND US.SemesterNum = 200801 ORDER BY UE.SectionId, UE.StuNum; -- Nested cursor OPEN @second_cur; FETCH NEXT FROM @second_cur into @a, @b, @c, @d; PRINT @y + ' Section ' + CONVERT(nVARCHAR(20), @z) PRINT '***' WHILE @@FETCH_STATUS = 0 -- Inner cursor loop (nested cursor while) BEGIN If @x = @a PRINT CONVERT(nVARCHAR(20), @b) + ' ' + @c + ' ' + @d; FETCH NEXT FROM @second_cur into @a, @b, @c, @d; END -- Inner cursor loop PRINT '=================================' FETCH NEXT FROM @first_cur INTO @x, @y, @z; CLOSE @second_cur; DEALLOCATE @second_cur; END -- Outer cursor loop CLOSE @first_cur; DEALLOCATE @first_cur;

  27. Notice that the second cursor is recreated each time you go through the first cursor loop. This can sometimes not be very efficient.

  28. Exception Handling • Opportunity to issue meaningful error messages • Exceptions

  29. System Messages • DIVIDE BY ZERO • Two ways of showing a user information about an error. • ERROR_NUMBER • ERROR_MESSAGE

  30. All we are doing here is forcing an error. The actual error does not make sense when it comes to INSERTING values

  31. Programmer-Defined Exceptions • One can create their own conditions and names • An example shown of how to define your own error messages • Life: Valid only for this block

  32. Triggers A database trigger is procedural code Automatically executes in response to certain events on a particular on a particular or view in a database. The trigger is mostly used for maintaining the integrity of the information on the database.

  33. An Example of a Login Trigger Create Table master.dbo.audit_logins (LoginName nVARCHAR(50), -- holds the login name LoginType nVARCHAR(50), -- holds the login type LoginTime DATETIME2, -- holds the time of the login ClientHost nVARCHAR(50)) -- holds the host from which the login request originated Create the Logon trigger: Create TRIGGER TR_audit_logins ON ALL SERVER WITH EXECUTE AS 'sa‘ FOR LOGON AS BEGIN declare @LogonTriggerData xml --XML datatype, stores XML data set @LogonTriggerData = eventdata(); Insert into master..audit_logins Select @LogonTriggerData.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(50)'), @LogonTriggerData.value('(/EVENT_INSTANCE/LoginType)[1]', 'varchar(50)'), @LogonTriggerData.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime'), @LogonTriggerData.value('(/EVENT_INSTANCE/ClientHost)[1]', 'varchar(50)') END

  34. Extensible Markup Language (XML) is a markup language that defines a set of rules for encoding documents in a format that is both human-readable and machine-readable. A markup language is a system for annotating a document in such a way that the syntax is distinguishable from the text. The design goals of XML emphasize simplicity, generality and usability across the Internet. <recipe> <title>Bread and Butter</title> <ingredientslist> <ingredient>Bread</ingredient> <ingredient>Butter</ingredient> </ingredientlist> <preparation> Put butter on a knife and spread on the bread. </preparation> </recipe>

  35. SELECT HOST_NAME() AS HostName, SUSER_NAME() LoggedInUser What EVENT_INSTANCE contains the following data in XML format <EVENT_INSTANCE> <EventType> event </EventType> <PostTime> datetime </PostTime> <SPID> spid </SPID> <ClientHost> host </ClientHost> <ServerName> name </ServerName> <LoginName> login </LoginName> <UserName> name </UserName> <DatabaseName> name </DatabaseName> <SchemaName> name </SchemaName> <ObjectName> name </ObjectName> <ObjectType> type </ObjectType> <TSQLCommand> command </TSQLCommand> </EVENT_INSTANCE>

  36. Simple Trigger Examples Will work with the FACTORY_WORKER table, used previously, and a new table FACTORY_WORKER_BACKUP CREATE TABLE FACTORY_WORKER_BACKUP (FWNumber NUMERIC(4) CONSTRAINT fw_backup_pk primary key, FWFirstName nVARCHAR(15), FWLastName nVARCHAR(20), FWRate NUMERIC(2)) CREATE TABLE FACTORY_WORKER (FWNumber NUMERIC(4) CONSTRAINT fw_pk primary key, FWFirstName nVARCHAR(15), FWLastName nVARCHAR(20), FWRate NUMERIC(2)) INSERT INTO FACTORY_WORKER_BACKUP VALUES (5001,'Philip','Brown',5) INSERT INTO FACTORY_WORKER_BACKUP VALUES (5002,'Jane','Adler',6) INSERT INTO FACTORY_WORKER_BACKUP VALUES (5003,'Cheryl','Black',4) INSERT INTO FACTORY_WORKER_BACKUP VALUES (5004,'James','Newton',7) INSERT INTO FACTORY_WORKER_BACKUP VALUES (5005,'Sandra','Jones',6) INSERT INTO FACTORY_WORKER VALUES (5001,'Philip','Brown',5) INSERT INTO FACTORY_WORKER VALUES (5002,'Jane','Adler',6) INSERT INTO FACTORY_WORKER VALUES (5003,'Cheryl','Black',4) INSERT INTO FACTORY_WORKER VALUES (5004,'James','Newton',7) INSERT INTO FACTORY_WORKER VALUES (5005,'Sandra','Jones',6)

  37. DML triggers execute when a user tries to modify data through a data manipulation language (DML) event such as INSERT, UPDATE, or DELETE statements on a table or view. • DML triggers can be used to: • enforce business rules • enforce data integrity • query other tables. • Can include complex Transact-SQL statements. The trigger and the statement that fires it are treated as a single transaction, which can be rolled back from within the trigger. If a severe error is detected (for example, insufficient disk space), the entire transaction automatically rolls back. • The most common table-oriented triggers: • INSERT, DELETE, and UPDATE • AFTER INSERT, AFTER DELETE, AFTER UPDATE • INSTEAD OF.

  38. Two special tables are used in trigger statements: • INSERTED: • The inserted table stores copies of the affected rows during INSERT and UPDATE statements. • During an insert or update transaction, new rows are added simultaneously to both the inserted table and the trigger table. • The rows in the inserted table are copies of the new rows in the trigger table. • DELETED: • The deleted table stores copies of the affected rows during DELETE and UPDATE statements. During the execution of a DELETE or UPDATE statement, rows are deleted from the trigger table and transferred to the deleted table. • The deleted table and the trigger table ordinarily have no rows in common.

More Related