Week 11

Question 1

A stored procedure has to be executed by a ________, while a trigger is executed by the _______ as the result of an event.

Answer 1

user, system

Question 2

Events that cause triggers to be activated include data ________, updates and ________.

Answer 2

Events that cause triggers to be activated include data inserts, updates and deletes.

Question 3

One drawback to using ________ instead of ___________ is that they cannot accept parameters.

Answer 3

triggers, stored procedures

Question 4

Stored procedure are for _______ actions or ________ actions

Answer 4

repetitive, complex

Question 5

Triggers are enforcing business rules or _________.

Answer 5

protection

Question 6

After creating a stored procedure what is the next step?

Answer 6

Parameters can be passed into the procedure

using @

Question 7

SQL Server has many built-in stored procedures, called _______ _________ ____________

Answer 7

System Stored Procedures

Question 8

Store procedure Usage Examples:

Answer 8

EXEC sp_tables
EXEC sp_tables @table_name = ‘Employee’
EXEC sp_tables @table_owner = ‘HumanResources’

Question 9

How to you create a stored procedure?

Answer 9

starts with EXEC with
prefix sp_(tablename)
add parameters with @(tablename)

Question 10

How do store procedure help the security of your database?

Answer 10

execute commands without permission in a controlled manner
•Execute As•Reduce/ eliminate SQL injection attacks
•Procedure can be encrypted to prevent copying and modifying
•Table/columns, etc are hidden from caller

Question 11

What are some benefits of stored procedures?

Answer 11

Speed
Easier maintenance
Reduced network traffic
security

Question 12

What commands in T-SQL are used to update a stored procedure and create a new one

Answer 12

UPDATE PROCEDURE

CREATE PROCEDURE

Question 13

What are the two types of triggers?•

Answer 13

Data Manipulation Language (DML)

Data Definition Language (DDL)