tailieunhanh - Database Management systems phần 3

Chúng tôi thảo luận về giá trị tương đối của hai phương pháp tiếp cận dịch hơn nữa sau khi xem xét làm thế nào để dịch bộ mối quan hệ với các ràng buộc tham gia vào các bảng. Hãy xem xét sơ đồ ER trong hình 3,13, trong đó cho thấy hai bộ mối quan hệ, Quản lý và Công trình Trong. kể từ khi nhiều tên SSN đã dname ngân sách | SQL Queries Programming Triggers 165 Examples of Triggers in SQL The examples shown in Figure written using Oracle 7 Server syntax for defining triggers illustrate the basic concepts behind triggers. The SQL 1999 syntax for these triggers is similar we will see an example using SQL 1999 syntax shortly. The trigger called initializes a counter variable before every execution of an INSERT statement that adds tuples to the Students relation. The trigger called increments the counter for each inserted tuple that satisfies the condition age 18. CREATE TRIGGER init_count BEFORE INSERT ON Students Event DECLARE count INTEGER BEGIN Action count 0 END CREATE TRIGGER incr_count AFTER INSERT ON Students Event WHEN 18 Condition new is just-inserted tuple FOR EACH ROW BEGIN Action a procedure in Oracle s PL SQL syntax count count 1 END Figure Examples Illustrating Triggers One of the example triggers in Figure executes before the activating statement and the other example executes after. A trigger can also be scheduled to execute instead of the activating statement or in deferred fashion at the end of the transaction containing the activating statement or in asynchronous fashion as part of a separate transaction. The example in Figure illustrates another point about trigger execution A user must be able to specify whether a trigger is to be executed once per modified record or once per activating statement. If the action depends on individual changed records for example we have to examine the age field of the inserted Students record to decide whether to increment the count the triggering event should be defined to occur for each modified record the FOR EACH ROW clause is used to do this. Such a trigger is called a row-level trigger. On the other hand the init_count trigger is executed just once per INSERT statement regardless of the number of records inserted because we have omitted the FOR EACH ROW phrase. Such a .