TRIGGERS-CURSOR

1. CHAPTER 9(tt) TRIGGERS-CURSOR

2. Trigger • Trigger is like a procedure that is automatically invoked by the DBMS in response to specified changes to data base • Trigger is like a ‘Daemon that monitors a data base, and is executed when the data base is modified in a way that matches the event specification • A data base that has a set of associated triggers is called an active database

3. Trigger Parts • Event • A change to data base that activates the trigger • Restriction • trigger restriction specifies a Boolean (logical) expression that must be TRUE for the trigger to fire • Action • A procedure that is executed when the trigger is activated. • Similar to stored procedures, a trigger action can contain PL/SQL statements

4. Types of Triggers • Row Triggers • A row trigger is fired each time the table is affected by the triggering statement. If a triggering statement affects no rows, a row trigger is not executed at all. • Statement Triggers • A statement trigger is fired once on behalf of the triggering statement, regardless of the number of rows in the table that the triggering statement affects (even if no rows are affected)

5. Trigger Timings • Before Trigger • Execute the trigger action before the triggering statement. Eliminate unnecessary processing of the triggering statement. • After Trigger • AFTER triggers are used when you want the triggering statement to complete before executing the trigger action

6. Create trigger • Syntax CREATE TRIGGER trigger_nameON { table | view } [ WITH ENCRYPTION ] {     { { FOR | AFTER | INSTEAD OF } { [DELETE] [,] [ INSERT ] [, ] [ UPDATE ] }         [ WITH APPEND ]         [ NOT FOR REPLICATION ]         AS sql_statement [ ...n ]     } }

7. Example: CREATE TRIGGER Trg_NgayLap_NgayGiaoHD ON Hoadon AFTER INSERT AS DECLARE @NgayLapHDDateTime, @NgayGiaoDateTime SELECT @NgayLapHD=hd.NGayLapHD,NgayGiao=hd.NgayGiaoNhan FROM HoaDonhd INNER JOIN Inserted i ON hd.MaHD=i.Mahd If @NgayGiao<@NgayLapHD BEGIN RAISERROR(500103,10,1) ROLLBACK TRANSACTION END

8. Example: AFTER TRIGGERS CREATETRIGGER Trg_Xoa_HD ON Hoadon AFTER DELETE AS SET NOCOUNT ON IF EXISTS (SELECT * FROM Deleted) BEGIN DELETE CT_HOADON WHERE CT_HOADON.MaHD IN (SELECT hd.MaHD FROM HoaDonhd INNER JOIN Deleted d ON hd.MaHD=d.Mahd) RAISERROR('Cac chi tiet HD da bi xoa',10,1) END SET NOCOUNT ON

9. After triggers • Example: CREATE TRIGGER NoDelete ON Product FOR DELETE AS IF(SELECT ProductID FROM Deleted )=12 BEGIN Print ‘You cannot delete the Productid=12’ RollBack transaction END

10. After triggers • Example: CRETE TRIGGER NoUppdate ON Product FOR Update IF Update(ProductID) BEGIN PRINT ‘You cannot update Productid’ RollBack Transaction END

11. After triggers Example: CREATE TRIGGER NoupdareOrders ON Orders FOR Update AS IF (Select OrderDate from Deleted) > Getdate() BEGIN Print ‘Ngay lap hoa don <=ngayhienhanh’ RollBackTransaction END

12. Altertriggers Example: ALTER TABLE [Order Details] DISABLE TRIGGER ALL • Delete trigger: • DROP TRIGGER Trigger_Name

13. Cursor • A cursor is a temporary work area created in the system memory when a SQL statement is executed. • A cursor contains information on a select statement and the rows of data accessed by it. This temporary work area is used to store the data retrieved from the database, and manipulate this data. • A cursor can hold more than one row, but can process only one row at a time. The set of rows the cursor holds is called the active set.

14. Types of cursors • Implicit cursors: These are created by default when INSERT, UPDATE, and DELETE statements are executedand when a SELECT statement that returns just one row is executed. • Explicit cursors: • They must be created when you are executing a SELECT statement that returns more than one row. • Although the cursor stores multiple records, only one record can be processed at a time, which is called as current row.

15. Declare cursor • Syntax DECLAREcursor_nameCURSOR[ LOCAL | GLOBAL ] [ FORWARD_ONLY | SCROLL ] [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ] [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] [ TYPE_WARNING ] FOR select_statement[ FOR UPDATE [ OF column_name [,...n ] ] ]

16. Declare cursor • Example: DECLARE var_rows number(5); BEGIN UPDATE employee SET salary = salary + 1000; IF SQL%NOTFOUND THEN dbms_output.put_line('None of the salaries where updated'); ELSIF SQL%FOUND THEN var_rows := SQL%ROWCOUNT; dbms_output.put_line('Salaries for ' || var_rows || 'employees are updated'); END IF; END;

17. Use cursor • Open cursor: • Fetch cursor: Access each line of data OPEN {cursor_name } FETCH [[NEXT | PRIOR | FIRST | LAST | ABSOLUTE n | RELATIVE n]] FROMcursor_name [INTO @variable_name [,...n ] ]

18. Use cursor • Delete data by cursor: • Close cursor: DELETE <Ten Table> WHERE CURRENT OF <Cur_Name> CLOSEcursor_name

19. Use cursor • DEALLOCATE: Removes a cursor reference DEALLOCATEcursor_name

20. Example: DECLARE MyCursor CURSOR FOR SELECT c.CustomerID,c.Companyname,c.contactname, o.OrderID,o.OrderDate FROM Customers c, Orders o WHERE c.CustomerID = o.CustomerID FOR UPDATE OPEN MyCursor DECLARE @cid VARCHAR( 8), @c VARCHAR( 80), @o INT, @od DATETIME, @cn VARCHAR( 80) FETCH NEXT FROM MyCursor INTO @cid, @c, @cn, @o, @od SELECT @cid BEGIN TRANSACTION UPDATE Customers SET CompanyName = 'q' WHERE CURRENT OF Mycursor DEALLOCATE MyCursor SELECT * FROM Customers ROLLBACK TRANSACTION