Transcript BA372 Stored Procedures and Triggers Lab

BA372 Stored Procedures and
Triggers Lab
What needs to be done to change a
customer’s credit limit?
• Who am I?
Get the user name from the system
Windows handles this when it connects to the DB
• May I?
A database lists users in roles
This role is called ChgClientCreditLimit
if ( (Select count(*) where Person, Role) > 0)
OK
• Do it
Update Clients Set CreditLimit=? , this customer
• Log it
Worked? Remember what was done by whom
Forbidden? Remember who tried
• Display
Tell the user what happened
C#.Net
Scenario 1 – Client Heavy
3 pages of C# code with embedded table/column
names, authorization rules, and business logic
Connect, Authenticate,
Check for success
Specify authorization parameters
Specify tables, columns, and SQL
Execute and check success
‘Blindly’ perform
SQL instructions
Specify update parameters
Specify tables, columns, and SQL
Execute and check success
Specify logging parameters
Specify tables, columns, and SQL
Execute and check success
Specify Results parameters
Specify tables, columns, and SQL
Execute and check success
Display results
DB Server
C#.Net
DB Server
Scenario 2 – Stored Proc
Connect, Authenticate,
Check for success
Specify authorization parameters
Specify tables, columns, and SQL
Execute and check success
Exec Stored Proc 
Specify update parameters
Specify tables, columns, and SQL
Execute and check success
Specify logging parameters
Specify tables, columns, and SQL
Halfand
thecheck
C# code
but
Execute
success
involved DB procedure
code:Results
authorization
logic,
Specify
parameters
logging
functions,
Specify
tables,
columns,and
and SQL
Execute
and checkdetails
successare
table/column
Display results
not included in the C#
program
Stored Procedure
ChgClientCreditLimit
C#.Net
DB Server
Scenario 3 – Proc + Trigger
Connect, Authenticate,
Check for success
Specify authorization parameters
Specify update parameters
Exec Stored Proc 
Display results
Stored Procedure
ChgClientCreditLimit
Logging is moved into a trigger. Changes are
logged no matter how the updates are
Specifycode,
tables,proc,
columns,
and SQL
made:
or utility
Execute and check success
In our lab, authorization is also moved to its
Specify
tables, ,which
columns,logs
anddenied
SQL
own proc,
AuthCheck
Execute and check success
attempts
Specify logging parameters
Specify tables, columns, and SQL
Execute Database
and checkTrigger
success
Logs the Activity
Specify Results parameters
Specify tables, columns, and SQL
Trigger Fires Automatically 
Things to Ponder
• Which solution has the most cohesive modules?
• How is data independence affected?
• Heterogeneity: Web? Automated? Mobile?
– What will an interface programmer need to know?
• Reliability, performance, and control
– DB locks, speed, memory, impact of an error, restoring
data, cross-platform consistency
– Compare the security of a single logging proc and auth
proc vs. SQL in multiple code modules
Moving functionality from client, to web server, to DB code profoundly affects a
variety of important issues. Which is best? IT DEPENDS