Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

Database Theory > Triggers / Procedures > Flashcards

Triggers / Procedures Flashcards

1
Q

What are triggers?

A

A trigger is a special stored procedure attached to a specific table. Managed by DBMS, supported by most RDBMS

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

What are the differences between triggers and stored procedures?

A
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

Give examples of triggers.

A
  • 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
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

Why use triggers?

A
  • Enforce business rules too complex for CHECK constraints or Referential Integrity
  • Automate activity
  • Access or modify other table
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

What is the downside of a trigger?

A

Downside: will slow down triggering operation

(INSERT, UPDATE or DELETE)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

What are stored procedures?

A

Pieces of executable code stored in the database (small programs) Managed by DBMS and supported by most RDBMS

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

Give examples of stored procedures.

A
  • 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
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

What are the main features of stored procedures?

A
  • 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, .
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

What is the preferred database access method?

A

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
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

Explain stored procedure body statements.

A
  • 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
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

What are the main features of Selected T-SQL?

A
  • CASE
  • DECLARE
  • EXECUTE
  • FETCH
  • RAISERROR
  • ROLLBACK
  • SET
  • Control of Flow
    • BEGIN/END
    • BREAK
    • CONTINUE
    • GOTO
    • IF/ELSE
    • RETURN
    • TRY/CATCH
    • WAITFOR
    • WHILE
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

How do you interact with stored procedures?

A
  • 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
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

Explain system stored procedures.

A
  • 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
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

Give an example of system stored procedures.

A

EXEC sp_columns ‘patients’

EXEC sp_columns @table_name=’patients’

EXEC sp_help

sp_help ‘patients’

sp_server_info

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

What is the structure of a stored procedure?

A
  • A stored procedure consists of:
    • Procedure name
    • Set of optional parameters
    • Routine body
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

Give a sample procedure.

A

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 well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

How would you improve procedure flexibility?

A

(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,

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

What is the ALTER SQL Statement?

A
  • 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
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

Give an example of 3rd parameter conditional updates.

A

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;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

Give an example of a 4th parameter procedure.

A
  • 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

21
Q

Give an example of the previous 4 param sample procedure.

A

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;

22
Q

What are variable declarations?

A
  • 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);

23
Q

How do you assign values to variables declared?

A

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);

24
Q

What are the conditional controls?

A
  • IF
  • IF-ELSE
  • CASE
25
Q

How does the if statement work?

A
  • 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’;

26
Q

How does the IF-ELSE statement work?

A

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’;

27
Q

How does the BEGIN-END statement work?

A
  • 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

28
Q

How do Nested IFs work?

A

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

29
Q

What is the CASE statement?

A
  • 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

30
Q

How does the WHILE statement work?

A
  • Loop while the condition is true
  • Used with BEGIN-END
  • Frequently used with cursors

WHILE @@FETCH_STATUS = 0

BEGIN

<loop></loop>

END

31
Q

How does the BREAK statement work?

A
  • Used to exit a WHILE loop

WHILE @@FETCH_STATUS = 0

BEGIN

<loop></loop>

IF @count = 5

BREAK;

END

32
Q

How does the CONTINUE statement work?

A
  • 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

33
Q

How do you delete a procedure?

A

DROP PROCEDURE procedure_name

34
Q

What are the Data Dictionary Tables for Procedures?

A
  • Every CREATE PROCEDURE statement generates entries in
    • sys.procedures
    • sys.sql_module
35
Q

What are Cursors?

A
  • 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
36
Q

What does the @@FETCH_STATUS do?

A
  • 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
37
Q

What is a good scenario to use triggers?

A
  • 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?
38
Q

Given the last scenario, What is the first business rule we can enforce using a trigger?

A
  • 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
39
Q

Continuing on the last scenario, what is another business rule we can enforce using a trigger?

A
  • 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
40
Q

How would you use a Trigger to perform calculations?

A

Use a trigger to calculate the order amount for the part based on the supplier chosen to provide the parts.

41
Q

Give a simple trigger example.

A

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;

42
Q

What are the Deleted and Inserted Tables?

A
43
Q

Give a more advanced trigger sample.

A

***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’;

44
Q

Given the last example, how would you fix the code to allow for multiple updated employees at once?

A

*** 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’;

45
Q

Given the result of the last trigger alter how would you alter the trigger to not revert when a single update was done improperly?

A

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’;

46
Q

What are the different ways to fire triggers?

A
  • 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
47
Q

When would one trigger case need for others?

A
  • 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
48
Q

How do you delete a trigger?

A

DROP TRIGGER Trigger_Name

49
Q

What are the Data Dictionary tables for triggers?

A
  • Every CREATE TRIGGER statement generates entries in
    • sys.triggers
    • sys.trigger_events
    • sys.events
    • sys.sql_modules

Database Theory (7 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions