Triggers / Procedures Flashcards
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
How does the if statement work?
- Tests a simple condition
- If the condition evaluates to TRUE, the next line of code is executed
- If the condition evaluates to FALSE, the control of the program is passed to the next statement after the test
IF @ref_error = 1
SET @error_msg = ‘NOT FOUND’;
How does the IF-ELSE statement work?
Similar to the IF structure
The difference is that when the condition evaluates to FALSE, the statement following the ELSE keyword is executed
eg.
IF @ref_error = 0
SET @error_msg = ‘FOUND’;
ELSE
SET @error_msg = ‘NOT FOUND’;
How does the BEGIN-END statement work?
- Used to enclose a block of statements where a single statement can be used
IF @ref_error = 0
BEGIN
SET @error_msg = ‘FOUND’;
SET @found = @found + 1;
END
ELSE
BEGIN
SET @error_msg = ‘NOT FOUND’;
SET @not_found = @not_found + 1;
END
How do Nested IFs work?
IF @evaluation = 100
SET @new_salary = salary * 1.3;
ELSE
BEGIN
IF @evaluation >= 90
SET @new_salary = salary * 1.2;
ELSE
SET @new_salary = salary * 1.1;
END
What is the CASE statement?
- Permits you to select an execution path based on multiple cases
- Two options for coding a CASE structure
- Example using the first option
eg.
CASE @evaluation
WHEN 100
THEN UPDATE employees SET salary = salary * 1.3;
WHEN 90
THEN UPDATE employees SET salary = salary * 1.2;
WHEN 80
THEN UPDATE employees SET salary = salary * 1.1;
ELSE UPDATE employees SET salary = salary * 1.05;
END
How does the WHILE statement work?
- Loop while the condition is true
- Used with BEGIN-END
- Frequently used with cursors
WHILE @@FETCH_STATUS = 0
BEGIN
<loop></loop>
END
How does the BREAK statement work?
- Used to exit a WHILE loop
WHILE @@FETCH_STATUS = 0
BEGIN
<loop></loop>
IF @count = 5
BREAK;
END
How does the CONTINUE statement work?
- Used to advance to the next iteration of a WHILE loop
WHILE @@FETCH_STATUS = 0
BEGIN
<loop></loop>
IF @price = 5.0
CONTINUE;
<loop></loop>
END
How do you delete a procedure?
DROP PROCEDURE procedure_name
What are the Data Dictionary Tables for Procedures?
- Every CREATE PROCEDURE statement generates entries in
- sys.procedures
- sys.sql_module
What are Cursors?
- Used to access a SELECT result set one row at a time
- Steps to use a cursor
- DECLARE a cursor with a SELECT statement
- OPEN the cursor
- This executes the SELECT statement and populates the cursor
- FETCH one row at a time from the result set INTO variables
- Each column fetched must have a correlating variable
- Perform whatever processing desired for each row
- CLOSE the cursor
- DEALLOCATE the cursor
What does the @@FETCH_STATUS do?
- Returns the status of the last cursor FETCH statement
- 0 = The FETCH statement was successful
- -1 = The FETCH statement failed or the row was beyond the result set
- -2 = The row fetched is missin
What is a good scenario to use triggers?
- A part can be supplied by many suppliers
- A supplier can supply many parts
- Sounds like M:N
- Introduce a Quote table, where Quote represents the price quoted on one Part by one Supplier
- What does the ERD look like now?
Given the last scenario, What is the first business rule we can enforce using a trigger?
- Business rule #1:
- No more than three suppliers are permitted to supply any single part
- A trigger can check how many rows already exist for a specific part and prevent an INSERT
Continuing on the last scenario, what is another business rule we can enforce using a trigger?
- Business rule #2:
- The lowest cost supplier will always be used
- A trigger can disallow any order that does not use the lowest quote for a par
How would you use a Trigger to perform calculations?
Use a trigger to calculate the order amount for the part based on the supplier chosen to provide the parts.
Give a simple trigger example.
USE joins;
GO
CREATE TRIGGER employees_insert
ON employees
AFTER INSERT
AS
UPDATE company_stats
SET number_of_employees = number_of_employees + 1;
Testing the Trigger
PRINT ‘**********Before Insert**********’
SELECT * FROM employees;
SELECT * FROM company_stats;
PRINT ‘**********Insert**********’
INSERT INTO employees (employee_id, first_name, last_name)
VALUES(110, ‘Bob’, ‘Loblaw’);
PRINT ‘**********After Insert**********’
SELECT * FROM employees;
SELECT * FROM company_stats;
What are the Deleted and Inserted Tables?
Give a more advanced trigger sample.
***If multiple rows are updated employees_update will fail***
SELECT @new_salary = (SELECT salary FROM insert.
test eg.
PRINT ‘**********Before Update**********’
SELECT * FROM employees WHERE department = ‘Sales’;
PRINT ‘**********Attempted Multiple Row Update**********’
UPDATE employees SET salary = 700 WHERE department = ‘Sales’;
GO
PRINT ‘**********After Update**********’
SELECT * FROM employees WHERE department = ‘Sales’;
Given the last example, how would you fix the code to allow for multiple updated employees at once?
*** ALL UPDATES REJECTED IF ONE UPDATE FAILS ***
ALTER TRIGGER employees_update
ON employees
AFTER UPDATE
AS
BEGIN
IF EXISTS (SELECT i.salary FROM inserted i
JOIN deleted d
ON i.employee_id = d.employee_id
WHERE i.salary > (d.salary * 1.5))
ROLLBACK TRANSACTION;
END
testing eg.
PRINT ‘**********Before Update**********’
SELECT * FROM employees WHERE department = ‘Sales’;
PRINT ‘**********Successful Multiple Row Update**********’
UPDATE employees SET salary = 600 WHERE department = ‘Sales’;
GO
PRINT ‘**********Attempted Single Row Update of 102**********’
UPDATE employees SET salary = 1500 WHERE employee_id = 102;
GO
PRINT ‘**********After Update**********’
SELECT * FROM employees WHERE department = ‘Sales’;
Given the result of the last trigger alter how would you alter the trigger to not revert when a single update was done improperly?
ALTER TRIGGER employees_update
ON employees
AFTER UPDATE
AS
DECLARE employees_cursor CURSOR
FOR SELECT i.salary, d.salary
FROM inserted i
JOIN deleted d
ON i.employee_id = d.employee_id;
DECLARE @new_salary MONEY;
DECLARE @old_salary MONEY;
BEGIN
OPEN employees_cursor;
FETCH NEXT FROM employees_cursor
INTO @new_salary, @old_salary;
WHILE @@FETCH_STATUS = 0
BEGIN
IF @new_salary > @old_salary * 1.5
BEGIN
ROLLBACK TRANSACTION;
BREAK;
END
FETCH NEXT FROM employees_cursor
INTO @new_salary, @old_salary;
END
CLOSE employees_cursor;
END
testing eg.
PRINT ‘**********Before Update**********’
SELECT * FROM employees WHERE department = ‘Sales’;
PRINT ‘**********Successful Multiple Row Update**********’
UPDATE employees SET salary = 750 WHERE department = ‘Sales’;
GO
PRINT ‘**********Attempted Single Row Update of 102**********’
UPDATE employees SET salary = 1500 WHERE employee_id = 102;
GO
PRINT ‘**********After Update**********’
SELECT * FROM employees WHERE department = ‘Sales’;
What are the different ways to fire triggers?
- Timing choices
- AFTER: after firing activity occurs
- INSTEAD OF: allows DBA to take complete control of modification
- Oracle, MySQL and DB2 also support BEFORE triggers
- AFTER example
- Insert, update or delete occurs first
- Trigger logic is executed
- Trigger can “roll back” data modification
When would one trigger case need for others?
- If an INSERT trigger is keeping the master table updated when new records are inserted into a child table
- What happens on UPDATEs or DELETES to the child table?
- Additional triggers are required to keep the tables in sync
How do you delete a trigger?
DROP TRIGGER Trigger_Name
What are the Data Dictionary tables for triggers?
- Every CREATE TRIGGER statement generates entries in
- sys.triggers
- sys.trigger_events
- sys.events
- sys.sql_modules
