Chapter 9 (tt): Triggers-Cursor

TRIGGERS-CURSOR CHAPTER 9(tt) 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 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 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) 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 Create trigger Syntax CREATE TRIGGER trigger_name ON { table | view } [ WITH ENCRYPTION ] { { { FOR | AFTER | INSTEAD OF } { [DELETE] [,] [ INSERT ] [, ] [ UPDATE ] } [ WITH APPEND ] [ NOT FOR REPLICATION ] AS sql_statement [ ...n ] } } Example: CREATE TRIGGER Trg_NgayLap_NgayGiaoHD ON Hoadon AFTER INSERT AS DECLARE @NgayLapHD DateTime, @NgayGiao DateTime SELECT @NgayLapHD=hd.NGayLapHD,NgayGiao=hd.NgayGiaoNhan FROM HoaDon hd INNER JOIN Inserted i ON hd.MaHD=i.Mahd If @NgayGiao<@NgayLapHD BEGIN RAISERROR(500103,10,1) ROLLBACK TRANSACTION END Example: AFTER TRIGGERS CREATE TRIGGER 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 HoaDon hd INNER JOIN Deleted d ON hd.MaHD=d.Mahd) RAISERROR('Cac chi tiet HD da bi xoa',10,1) END SET NOCOUNT ON 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 After triggers  Example: CRETE TRIGGER NoUppdate ON Product FOR Update IF Update(ProductID) BEGIN PRINT ‘You cannot update Productid’ RollBack Transaction END After triggers Example: CREATE TRIGGER NoupdareOrders ON Orders FOR Update AS IF (Select OrderDate from Deleted) > Getdate() BEGIN Print ‘Ngay lap hoa don <=ngay hien hanh’ RollBack Transaction END Alter triggers Example: ALTER TABLE [Order Details] DISABLE TRIGGER ALL Delete trigger: DROP TRIGGER Trigger_Name 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. Types of cursors Implicit cursors: These are created by default when INSERT, UPDATE, and DELETE statements are executed and 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. Declare cursor Syntax DECLARE cursor_name CURSOR [ 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 ] ] ] 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; Use cursor Open cursor: Fetch cursor: Access each line of data OPEN {cursor_name } FETCH [[NEXT | PRIOR | FIRST | LAST | ABSOLUTE n | RELATIVE n]] FROM cursor_name [INTO @variable_name [,...n ] ] Use cursor Delete data by cursor: Close cursor: DELETE WHERE CURRENT OF CLOSE cursor_name Use cursor DEALLOCATE: Removes a cursor reference DEALLOCATE cursor_name  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