Views, stored procedures and triggers. Transactions. Module 10

1.

Views, stored
procedures and
triggers. Transactions
• Module 10

2.

Agenda
• Views
• Stored procedures
• Triggers
• Transactions
2

3.

Views
• Special DB objects aimed to simplify
creation of complex SQL queries
• Encapsulate SELECT queries so that they
could be accessed in another queries just
as they would be regular tables
3

4.

Example of a View
CREATE VIEW VW_EMPLOYEE
AS
SELECT
E1.ID,
E1.FIRSTNAME,
E1.LASTNAME,
D.NAME DEPNAME,
D.CITY,
E2.FIRSTNAME BOSS_FIRSTNAME,
E2.LASTNAME BOSS_LASTNAME
FROM EMPLOYEE E1
LEFT JOIN EMPLOYEE E2
ON E1.ID_BOSS = E2.ID
LEFT JOIN DEPARTMENT D
ON E1.ID_DEPARTMENT = D.ID
4

5.

Example of a View
SELECT * FROM VW_EMOPLOYEE
5

6.

Stored Procedures
• A stored procedure is a subroutine
available to applications that access a
relational database system
• Usually is written in special language which
is the extension of SQL.
• For MS SQL Server this language is called
Transact-SQL
6

7.

Advantages of SP
• Performance
• Testing
• Scalability
• Maintainability
• Security
• etc.
7

8.

Example of a Stored Procedure
CREATE PROCEDURE spAvgRateByCity
@City VARCHAR(30),
@AvgRate NUMERIC(10, 5) OUTPUT
AS
SELECT @AvgRate = AVG(E.RATE)
FROM EMPLOYEE E
JOIN DEPARTMENT D
ON E.ID_DEEPARTMENT = D.ID
WHERE D.CITY = @City
8

9.

Stored Procedures Execution
DECLARE @AvgRate NUMERIC(10, 5)
EXEC spAvgRateByCity ‘Lviv',
@AvgRate OUTPUT
SELECT @AvgRate
9

10.

Triggers
• Procedural code that is automatically
executed in response to certain events on a
particular table or view in a database.
• The trigger is mostly used for maintaining
the integrity of the information on the
database.
10

11.

Types of Triggers
• AFTER triggers are executed after the
action of the INSERT, UPDATE, or DELETE
statement is performed
• INSTEAD OF triggers override the
standard actions of the triggering
statement.
The primary advantage of INSTEAD OF
triggers is that they enable views that would
not be updatable to support updates
11

12.

Example of a Trigger
CREATE TABLE EMP_AUDIT (ID INT IDENTITY, USERNAME VARCHAR(MAX),
ACTION_TYPE VARCHAR(1),
NOTE VARCHAR(MAX), ACTION_TIME datetime);
CREATE TRIGGER T_INSERT_EMPLOYEE
ON EMPLOYEE AFTER INSERT
AS
BEGIN
INSERT INTO EMP_AUDIT
(USERNAME, ACTION_TYPE, NOTE, ACTION_TIME)
SELECT
CURRENT_USER, 'I',
concat(I.ID , '|' , I.LASTNAME , '|' , I.FIRSTNAME) NOTE,
CURRENT_TIMESTAMP
FROM INSERTED I
END;
12

13.

Example of a Trigger (2)
CREATE TRIGGER T_UPDATE_EMPLOYEE
ON EMPLOYEE AFTER UPDATE
AS
BEGIN
INSERT INTO EMP_AUDIT
(USERNAME, ACTION_TYPE, NOTE, ACTION_TIME)
SELECT
CURRENT_USER,'U',
concat( I.ID , '|' , D.LASTNAME , '->' , I.LASTNAME , '|' ,
D.FIRSTNAME , '->' , I.FIRSTNAME),
CURRENT_TIMESTAMP
FROM INSERTED I LEFT JOIN DELETED D ON I.ID = D.ID
END
13

14.

Testing the Trigger
INSERT INTO EMPLOYEE
(ID, LASTNAME, FIRSTNAME, POSITION,
DATE_EMPLOYMENT,
ID_DEPARTMENT, ID_BOSS, RATE, BONUS)
VALUES
(25, 'Семенюк', ‘Макар', 'Seller',
'12/06/2000', 5, 1, 3500,
500);
SELECT * FROM EMP_AUDIT
14

15.

Testing the Trigger (2)
UPDATE EMPLOYEE
SET LASTNAME = 'Прокопів'
WHERE ID = 26;
SELECT * FROM EMP_AUDIT
15

16.

Transactions
• A transaction is a sequence of operations
performed as a single logical unit of work
• A transaction is a series of database
operations either all occur, or nothing
occurs
16

17.

Key properties – ACID
• Atomicity
• Consistency
• Isolation
• Durability
17

18.

Atomicity
• “All or nothing": if one part of the
transaction fails, the entire transaction fails,
and the database state is left unchanged
18

19.

Consistency
• Ensures that any transaction will bring the
database from one valid state to another.
19

20.

Isolation
• Ensures that the concurrent execution of
transactions results in a system state that
would be obtained if transactions were
executed serially, i.e., one after the other
• In case when two concurrent transactions
attempt to update the same data, one of
them will be paused rolled back.
20

21.

Durability
• Ensures that once a transaction has been
committed, it will remain so, even in the
event of power loss, crashes, or errors.
21

22.

Commands for transaction control
• BEGIN TRAN
(or BEGIN TRANSACTION)
• COMMIT TRAN
(or COMMIT TRANSACTION)
• ROLLBACK TRAN
(or ROLLBACK TRANSACTION)
22

23.

Transaction example
BEGIN TRAN
UPDATE EMPLOYEE
SET RATE = RATE * 1.2
WHERE POSITION = 'Seller';
UPDATE EMPLOYEE
SET RATE = RATE * 1.3
WHERE POSITION = 'Consultant'
IF @@ROWCOUNT <= 2
COMMIT TRAN
ELSE ROLLBACK TRAN
23

24.

Managing transactions: other approach
• There is a possibility to manage DB
transaction from upper layers of
application (various components and
frameworks)
• There are some frameworks and patterns
for high level programming languages
which implement some features of
transactions, i.e. ORM systems, the “unit of
work” pattern etc.
24

25.

More information
• https://technet.microsoft.com/enus/library/aa214068%28v=sql.80%29.aspx
• https://technet.microsoft.com/enus/library/aa174792%28v=sql.80%29.aspx
• https://technet.microsoft.com/enus/library/aa213995%28v=sql.80%29.aspx
• https://msdn.microsoft.com/library/ms188
929%28v=sql.120%29.aspx
25

26.

Thank you!
US OFFICES
EUROPE OFFICES
Austin, TX
Fort Myers, FL
Lehi, UT
Newport Beach, CA
Waltham, MA
Bulgaria
Germany
Netherlands
Poland
Russia
Sweden
Ukraine
United Kingdom
www.softserveinc.com