What are triggers?
A trigger is a special stored procedure attached to a specific table. Managed by DBMS, supported by most RDBMS
What are the differences between triggers and stored procedures?
Give examples of triggers.
- Reduce inventory when an item is sold
- Permit changes to employee records during business hours only
- Automatically ensure the salary is within range for
- the job when an employee receives a job change
- Audit trail / activity logging
- Record changes to bank account
- Withdrawal, deposit, modifying customer profile
- Record changes to bank account
Why use triggers?
- Enforce business rules too complex for CHECK constraints or Referential Integrity
- Automate activity
- Access or modify other table
What is the downside of a trigger?
Downside: will slow down triggering operation
(INSERT, UPDATE or DELETE)
What are stored procedures?
Pieces of executable code stored in the database (small programs) Managed by DBMS and supported by most RDBMS
Give examples of stored procedures.
- Archive last year’s data
- Remove customers data when the customer hasn’t bought anything for more than 3 years
- Implement a price change across a category of products
What are the main features of stored procedures?
- Automate and standardize processes
- Compiled, therefore faster
- Usually written in Transact-SQL (T-SQL)
- Can also be written in a Common Language Runtime (CLR) programming languages: C#, Visual Basic.NET, .
What is the preferred database access method?
C#, VB.NET, Java, … can form SQL and execute it
This is not recommended
- Best practice is for programming languages to call stored procedures for database access
- Executes faster
- Reduces network traffic
- More secure
- Consistent
- DBA approve
Explain stored procedure body statements.
- SQL is non-procedural and set oriented
- Stored procedures require procedural code
- Each database vendor extends SQL with procedural code
- Microsoft uses T-SQL
- Oracle uses Procedural Language SQL (PL/SQL)
- IBM uses SQL Procedural Language (SQL PL)
- MySQL uses SQL/Persistent Stored Module (SQL/P
What are the main features of Selected T-SQL?
- CASE
- DECLARE
- EXECUTE
- FETCH
- RAISERROR
- ROLLBACK
- SET
- Control of Flow
- BEGIN/END
- BREAK
- CONTINUE
- GOTO
- IF/ELSE
- RETURN
- TRY/CATCH
- WAITFOR
- WHILE
How do you interact with stored procedures?
- Invoked by EXECUTE (EXEC) statement in T-SQL
- EXEC is optional, but a good idea
- It allows auto complete to help
- Can accept command line parameters, separated by commas
- Parameters can be positional or name
Explain system stored procedures.
- SQL Server provides many stored procedures
- Many are used for database administration
- Typing the name of a system stored procedure in a query window and pressing F1 will get help on the procedure
- Right clicking the procedure in the Object Explorer and selecting Modify will reveal the procedure code
- sp_columns, sp_help, sp_server_info
Give an example of system stored procedures.
EXEC sp_columns ‘patients’
EXEC sp_columns @table_name=’patients’
EXEC sp_help
sp_help ‘patients’
sp_server_info
What is the structure of a stored procedure?
- A stored procedure consists of:
- Procedure name
- Set of optional parameters
- Routine body
Give a sample procedure.
Increase and vendor are hard coded, not very flixible.
CREATE PROCEDURE update_items_item_cost
AS
UPDATE items
SET item_cost = item_cost * (1 + 0.25)
WHERE primary_vendor_id = 1;
How would you improve procedure flexibility?
(NOT FLIXIBLE)
PRINT ‘**********Before Update**********’
SELECT * FROM items ORDER BY primary_vendor_id;
EXEC update_items_item_cost;
PRINT ‘**********After Update**********’
SELECT * FROM items ORDER BY primary_vendor_id;
VERSUS
(FLIXIBLE) Increase and vendor are parameters,
What is the ALTER SQL Statement?
- ALTER PROCEDURE allows changing an existing stored procedure
- ALTER TABLE allows for changing table structure
- Adding/Removing/Changing of columns, constraints, …
- ALTER DATABASE, ALTER VIEW
Give an example of 3rd parameter conditional updates.
ALTER PROCEDURE update_items_item_cost
@increase DECIMAL(3, 2),
@vendor_id INT,
@update INT = 0 – If @update = 1, perform update, else show what would be updated
AS
BEGIN
IF @update = 1
UPDATE items
SET item_cost = item_cost * (1 + @increase)
WHERE primary_vendor_id = @vendor_id;
ELSE
SELECT item_id, vendor_name, item_cost AS [existing_cost], item_cost * (1 + @increase) AS [proposed_cost]
FROM items
JOIN vendors
ON primary_vendor_id = vendors.vendor_id
WHERE primary_vendor_id = @vendor_id;
END
To test;
PRINT ‘**********Before Update**********’
SELECT * FROM items WHERE primary_vendor_id = 1;
PRINT ‘**********No Update**********’
EXEC update_items_item_cost @increase=0.25, @vendor_id=1;
PRINT ‘**********After No Update**********’
SELECT * FROM items WHERE primary_vendor_id = 1;
PRINT ‘**********Update**********’
EXEC update_items_item_cost @increase=0.25, @vendor_id=1, @update=1;
PRINT ‘**********After Update**********’
SELECT * FROM items WHERE primary_vendor_id = 1;
Give an example of a 4th parameter procedure.
- 4th parameter will be returned to caller with number of rows affected
ALTER PROCEDURE update_items_item_cost
@increase DECIMAL(3, 2),
@vendor_id INT,
@update INT = 0, – If @update = 1, perform update, else show what would be updated
@number_of_records INT OUTPUT
AS
DECLARE items_cursor CURSOR
FOR SELECT item_cost
FROM items
WHERE primary_vendor_id = @vendor_id
FOR UPDATE;
DECLARE @item_cost DECIMAL(9, 2);
BEGIN
IF @update = 1
BEGIN
SET @number_of_records = 0;
OPEN items_cursor;
FETCH NEXT FROM items_cursor
INTO @item_cost;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @item_cost = @item_cost * (1 + @increase);
UPDATE items
SET item_cost = @item_cost
WHERE CURRENT OF items_cursor;
SET @number_of_records = @number_of_records + 1;
FETCH NEXT FROM items_cursor
INTO @item_cost;
END
CLOSE items_cursor;
END
ELSE – No Update
SELECT item_id, vendor_name, item_cost AS [existing_cost], item_cost * (1 + @increase) AS [proposed_cost]
FROM items
JOIN vendors
ON primary_vendor_id = vendors.vendor_id
WHERE primary_vendor_id = @vendor_id;
DEALLOCATE items_cursor;
END
Give an example of the previous 4 param sample procedure.
PRINT ‘**********Before Update**********’
SELECT * FROM items WHERE primary_vendor_id = 1;
– Need to declare a variable to receive output
DECLARE @num_of_rows INT;
PRINT ‘**********No Update**********’
EXEC update_items_item_cost @increase=0.25, @vendor_id=1, @update=0, @number_of_records=@num_of_rows OUTPUT;
SELECT @num_of_rows AS [number_of_rows];
PRINT ‘**********After No Update**********’
SELECT * FROM items WHERE primary_vendor_id = 1;
PRINT ‘**********Update**********’
EXEC update_items_item_cost @increase=0.25, @vendor_id=1, @update=1, @number_of_records=@num_of_rows OUTPUT;
SELECT @num_of_rows AS [number_of_rows];
PRINT ‘**********After Update**********’
SELECT * FROM items WHERE primary_vendor_id = 1;
What are variable declarations?
- Declare local variables used for
- Calculations
- Assignment to output parameters
- Assignment to columns for database updates
- As input parameters passed by calling programs
- Error handling
eg.
DECLARE @total_sales
DECIMAL(11,2);
DECLARE @number_customers INT;
DECLARE @error_msg CHAR(10);
DECLARE @order_date DATETIME;
DECLARE @picture VARBINARY(MAX);
How do you assign values to variables declared?
With the Assignment Statement SET.
- Used to assign values to
- Input and output parameters
- Local variables
- Conform to SQL arithmetic operators
- Compatible data types of target and source
eg.
SET @record_count = @record_count + 1;
SET @credit_limit = @credit_limit * 1.20;
SET @num_orders = NULL;
SET @max_credit_limit = (SELECT MAX(credit_limit) FROM
customers);
What are the conditional controls?
- IF
- IF-ELSE
- CASE
