Sybase Session 4-document

SYBASE SESSION 4-DOCUMENT

___________________________________________________________________________________________________ Copyrights Reserved Page 1 of 11

TRANSACT-SQL PROGRAM STRUCTURES & TRANSACTION MANAGEMENT Chapter Includes: Triggers Magic

Tables

Nested Stored

Triggers Procedures

Optimizing Remote

Stored procedures

Stored procedures

Transaction

Mechanism

Transaction

Control

Transaction

Programming

Transactions

and Locking

Transactions

and Triggers

Transactions

and Stored Procedures

Chained

Transactions

___________________________________________________________________________________________________ Copyrights Reserved Page 2 of 11

Triggers: Triggers are database objects that are bound directly to tables to enforce the referential integrity. Triggers are bound to the table for various purposes such as insertion, updating and deletion. Types of Trigger: There are 3 types of Triggers Insert

Trigger

Update Delete

Trigger and

Trigger

Insert Trigger: A trigger is created on insert statements and when ever a statement is created, then the trigger will be fired. That means unless the trigger fires, the statement is not completed. Update Trigger: A Trigger is created on update statement and when ever a statement is updated, and then the trigger will be fired. That means unless the trigger fires, the statement is not updated. Delete Trigger: A Trigger is created on delete statement and when ever a statement is deleted, and then the trigger will be fired. That means unless the trigger fires, the statement is not deleted. Syntax for creating a Trigger: create trigger trigger_name on table_name for {insert:update:delete} as sql statement. ___________________________________________________________________________________________________ Copyrights Reserved Page 3 of 11

Example : create trigger titles_trigger on titles for insert as print “title inserted”

Magical Tables: Whenever a trigger is updated, internally two tables will be formed. First table is for the previous (statement before update) statement and second table filled with the modified statement. So by going through the first table we can identify the number of tables that are updated. As these two tables are formed automatically for every update trigger, they are called Magic tables.

Nested Triggers: If a trigger performs an insert, update or delete on another table that has trigger defined for that action, by default that trigger will also fire. This is called nested trigger. Triggers are limited to 16levels of nesting. Check @@nestlevel to avoid exceeding limits.

Stored Procedures: A stored procedure is a database object that exists independently of a table. It can be called from client, parameters may be passed and returned, and error codes may be checked. Advantages of Stored Procedure: Faster

execution

Reduced Modular

Network Traffic Programming

Restricted

Function Based access to Tables

Reduced

Operator error

Enforced

consistency

Automated

complex or sensitive transactions.

___________________________________________________________________________________________________ Copyrights Reserved Page 4 of 11

Syntax for creating a stored procedure: create proc procedure_name as sql statement Example: create proc pub_titles as select t.title, p.pubname from publisher p, titles t where p.pb_id = t.pub_id

Optimizing Stored Procedures: 

The SQL server optimizer generates a query plan for a stored procedure based on the parameters passed in the first time it is executed. This query plan is then run from cache for subsequent executions. To force a new query plan we use recompile option.

Remote Stored Procedures: 

Remote procedures are procedures residing on the other servers. Fully qualify the stored procedure name with the server name to execute procedures on other servers.

Syntax: [exec[ute]] server_name.db_name.owner.proc_name Transaction Mechanism: There are four aspects of transaction processing SQL 

Server maintains a transaction log

SQL server locks table pages during transactions so other users cannot

access the changing data. 

SQL Server performs automatic recovery upon restart.



Transaction control statements in transact-SQL determine when a

transaction begins and commits or rolls back.

___________________________________________________________________________________________________ Copyrights Reserved Page 5 of 11

Transaction Control: All SQL statements are said to be transactions. They can be controlled by transaction control syntax to group sets of SQL statements to single logical work units. There are three types of transaction control process are there. BEGIN

TRANSACTION

COMMIT ROLL

TRANSACTION:

:

it starts a unit of work. it completes a unit of work.

BACK TRANSACTION: it cancels a unit of work.

Transaction Programming: The programming issues associated with writing transactional SQL are fairly straightforward. Once you issue a Begin tran statement. The server performs all the subsequent work without formally writing a final record of the work.

Transaction and Locking: In order to ensure data integrity, SQL Server places exclusive locks on data pages involved in a transaction. As SQL Server progress through the query, locks are acquired on each table modified by a statement and are then held until SQL Server reaches a commit tran that sets @@trancount to 0.

___________________________________________________________________________________________________ Copyrights Reserved Page 6 of 11

Transaction and Triggers: Triggers are considered to be an integral part of the transaction in which a data modification is executed. Example: begin tran update titles set price =$99 where title_id = 'bu1234' commit tran

Rollback Transaction in a Trigger: Rollback transaction inside a trigger - in addition to reversing the effect of current data modification statement, immediately return from the trigger and abort the batch, returning to no automatic error message.

Rollback Trigger: It provides a method with in a trigger for rolling back a single statement in a transaction without affecting the rest of the transaction. The effect of the rollback trigger is to rollback entire transaction and abort processing of that batch immediately.

___________________________________________________________________________________________________ Copyrights Reserved Page 7 of 11

Transactions in Stored Procedures: The stored procedures and the batches that call those procedures need to be consistent with each other to provide with proper data integrity control. One must establish coding standards for that enable transactional control to work whether the transaction commits successfully or the transaction is rolled back. There are several methods of coding stored procedures with transactions to ensure that the procedure works properly as a standalone transaction or as a part of larger, nested transaction. Chained Transactions: Chained transactions are alternative transaction processing capabilities to provide a better compatibility with other front ends in the area of transaction processing. Chained mode eliminates nesting. Here every SQL data retrieval or data modification statement initiates a transaction that needs subsequently to be closed with a commit statement or canceled with a rollback. 'Set chained on' is used to turn on chained mode. 1) How the transaction control statements affect @@trancount Statement value of @@trancount 1.Begin tran ----2.Commit tran ----3.Save tran ----4.Rollback trigger ----5.Rollback tran ----6.Rollback tran save item -----

2) What is difference between chained and unchained mode in transaction? In which transaction non-repeatable reads are eliminated?

3) What are the limitations on? ___________________________________________________________________________________________________ Copyrights Reserved Page 8 of 11

Trigger Stored

procedures

4) Explain the characteristic features of the Transaction 5) Give the meanings for the following status codes -5,-4,-2,-9 6) Explain about the Cursors in a Stored procedure? How do you replace a cursor in a stored procedure? 7) State true or false 1) SQL Server notes the transaction nesting level before calling a stored procedure 2) A Stored procedure may not create view, default, rule and trigger 3) SQL server optimizer generates a query plan for a stored procedure based on the parameters passed in the first time it is executed. DATABASE SCHEMA Under consideration is the database of naval ships that took part in World War II. The database has the following relations: Classes(class,type,country,numGuns,bore,displacement) Ships(name,class,launched) Battles(name,date) Outcomes(ship,battle,result) Ships in classes are arranged to a single project. A class is normally assigned the name of the first ship in the class under consideration (head ship); otherwise, class name does not coincide with any ship name in the database). The relation Classes includes the class name, type (bb for a battle ship, or bc for a battle cruiser), country the ship was built, number of main guns, gun caliber (diameter of the gun barrel, in inches), and displacement (weight in tons). The relation Ships includes the ship's name, its class name, and launch year. The relation Battles covers the name and date of the battle the ships participated; while the result of their participation in the battle (sunk, damaged, or unharmed OK) is in the relation Outcomes. Note: the relation Outcomes may include the ships not included in the relation Ships. ___________________________________________________________________________________________________ Copyrights Reserved Page 9 of 11

Based On the above Database schema solve the Following queries 1)Modify data in Classes table so that gun calibers are measured in centimeters (1 inch = 2.5cm), and the displacement - in metric tons (1 metric ton = 1.1 tons). Calculate displacement to within integer. 2)Delete from Ships table all the ships that belong to USA. 3)Find the names of ship in the ships sunk in battles and the names of corresponding battles. 4)Determine names of all the ships in Ships table that meet a combination of at least four criteria from the following list: numGuns = 8 bore = 15 displacement = 32000 type = bb launched = 1915 class = Kongo country = USA List the names of head ships in the database (taking into account Outcomes table).

Topics Covered: Transact-SQL Program Structures -(171) ___________________________________________________________________________________________________ Copyrights Reserved Page 10 of 11

Transaction Management

-(201)

Book for Reference: Sybase SQL Server 11 by Ray Rankins Jeffrey R. Garbus David Solomon

___________________________________________________________________________________________________ Copyrights Reserved Page 11 of 11