SQL Server

Application of Triggers and Understanding the Transaction Procedures in Database Part – 4

Application of Triggers and Understanding the Transaction Procedures in Database Part – 4

Handling Trigger

When an individual is dealing with trigger, they can do the subsequent actions on a trigger:-

· Delete trigger

· Alter trigger

Delete a Trigger

As and when the prerequisite alters, an individual might want to remove certain triggers form the database or tables / relations. For an instance, an individual have a trigger named trgLoansSanctionPendingDelete which gives a delete message after an existing data from the LoansSanctionPending table / relation is deleted. Now, the management has decided that the loans which were pending for sanction will not be deleted. Hence, an individual want to delete that particular trigger. To do this job, an individual can use the DROP TRIGGER command. The code of the DROP TRIGGER command is as follows:-

DROP TRIGGER { My_Trigger_Name }

Here,

· My_Trigger_Name – It is the name of the particular trigger that an individual desires to delete.

The subsequent command deletes the trigger trgLoansSanctionPendingDelete:-

DROP TRIGGER trgLoansSanctionPendingDelete

Alter a Trigger

As a database designer, an individual may want to alter the sensibleness or syntax of a trigger. For an instance, a trigger is castoff to compute 11.05% rate of interest on every car loan. By means of the fresh banking strategy, the rate of interest for the car loan has been decreased to 9.85%. To imitate this alteration in the trigger, an individual want to alter the syntax of the trigger. For this, an individual can use the ALTER TRIGGER command to alter the trigger. The code of ALTER TRIGGER command is as follows:-

ALTER TRIGGER My_Trigger_Name

{ FOR | AFTER | INSTEAD OF } { INSERT | UPDATE | DELETE | DDL_DATABASE_LEVEL_EVENTS }

{ AS { SQL_Commands } }

Think through an instance, whenever a customer pays back the entire loan amount with the pre – mentioned interest rate, the loan status is altered in the CustomerDetails table / relation. Afterwards the loan status is altered; the LastUpdated column / attribute of the CustomerDetails table / relation is also set to the present date, but the LoanDetails table / relation must be updated and the loan status should be set to ‘Paid’

An individual can alter the trgLoanDetails trigger which was formed previously to examine if the LastUpdate column / attribute is present date or not. If the LastUpdate column / attribute is not the present date, then the trigger must show an error message. Now, one more function need to be added to this trigger, which is the loan status modification in the LoanDetails table / relation. To modify the trgLoanDetails trigger, an individual must implement the subsequent commands:-

ALTER TRIGGER trgLoanDetails

ON LoanDetails

FOR INSERT , UPDATE

AS

DECLARE @ LastUpdate

DECLARE @ LoanStatus

DECLARE @ LoanID

SELECT @ LastUpdate = LastUpdate FROM INSERTED

SELECT @ LoanStatus = LoanStatus FROM INSERTED

SELECT @ LoanID = LoanID FROM INSERTED

IF ( @ LastUpdate ! = GETDATE ( ) )

BEGIN

PRINT ‘ The Last Update date must be the present date. Therefore, data cannot be inserted. Try Again.. ’

ROLLBACK TRANSACTION

END

ELSE IF ( @ LastUpdate = GETDATE ( ) AND @ LoanStatus = ‘ Paid ’ )

BEGIN

UPDATE LoanDetails SET LoanStatus = ‘ Paid ’ WHERE LoanID = @ LoanID

PRINT ‘ The LoanDetails table is also updated. ’

END

RETURN

Execution of Transaction in SQL Server

Often a time, an individual wants to run a series of commands as a sole consistent part of task. For this type of condition, an individual wants to execute either the entire commands successfully or wants not a single command to be run successfully. This in turn benefits in confirming the information reliability.

In SQL Server, an individual can apply transaction to make sure the integrity of information is maintained. In a multi – customer atmosphere, there can be numerous transactions gaining access to the similar source at the same point of time. To stop faults which may happen due to the transactions gaining access to the identical source, an individual can practice using SQL Server Locks. The Locks offer a technique to protect a source up until one transaction is completed so as to that solely one transaction can work on a database source at a single point of time.

Forming the Transaction

The transaction can be demarcated as an arrangement of actions to be completed collectedly as a sole coherent piece of task. A particular piece of task should have the four (4) things named as ACID (Atomicity, Consistency, Isolation, and Durability).

· Atomicity – It stipulates that either the entire information alterations will be done or nothing will be done.

· Consistency – It stipulates that the entire information is in a steady condition once a transaction is finished effectively. Every guideline in a Relational Database Management System (RDBMS) should be applied to the alterations in a transaction to preserve total information reliability.

· Isolation – It stipulates that whichever information alteration completed by means of simultaneous transactions should be inaccessible from the alterations completed by means of another simultaneous transaction. In modest verses, a transaction should obtain information either in the condition in which it was before a simultaneous transaction has altered it or in the condition in which it is after the second transaction has been completed. There is no possibility for the transaction to realize a midway condition.

· Durability – It stipulates that if any alteration has been made in information by means of a finished transaction should stay forever in influence in the database system. Thus, any change in information owing to a finished transaction continues to stay even in the occasion of a system failure. This is confirmed using the theory of back up plus restore of transaction logs.

It is quite significant that a database system offers techniques to confirm the physical reliability of every single transaction. To accomplish the necessity of the ACID (Atomicity, Consistency, Isolation, and Durability) things, the SQL Server offers the subsequent essentials:-

· Transaction Administration – This confirms the atomicity as well as the consistency of every single transaction. A transaction should be positively accomplished either after it has been started or the SQL Server revert back the entire information alterations made from the time when the transaction has started.

· Locking – This conserves transaction’s durability along with the isolation.

The SQL Server permits applying transactions in the subsequent two (2) methods:-

· Auto commit transaction

· Explicit transaction

Auto Commit Transaction

Auto commit transaction is the default transaction administration style of the SQL Server. Depending on the comprehensiveness of every single T-SQL commands, transactions are spontaneously committed otherwise revert back. A command is committed when it is concluded effectively as well as fruitfully and the command is reverted back when it meets an error. While working in auto commit method, an individual can practice the BEGIN TRANSACTION command to dominate the default auto commit style. The SQL Server yields to the auto commit style as soon as the explicit transaction is committed or else reverted back.

Explicit Transaction

Explicit transaction is such a method where the beginning as well as the finish of the transaction is demarcated explicitly. Explicit transaction is termed as user – defined or else as user – indicated transaction in previous versions of the SQL Server. Explicit transaction is indicated by means of the BEGIN TRANSACTION and COMMIT TRANSACTION commands.

The upcoming article will show how to Begin a Transaction and Commit a Transaction. Furthermore, Backsliding the Transaction and Applying Transactional Reliability will also be discussed.