A Quick Introduction to Stored Procedure and Trigger Syntax

1. A Quick Introduction to Stored Procedure and Trigger Syntax To Proc or not to Proc Technical Details Multi-Platform Implications Passing Parameters vs. Specifying SQL Syntax Data Independence Storing Code Modules in the Database Performance (?) Module Independence (Coupling and Cohesion) Returning Values DB Utilities and Reliance on System Data Creating Variables • Development Team • Skills • Size • Risks Inserted and Deleted ‘tables’ in Triggers

2. A Familiar Task Does this table structure look familiar? CREATE TABLE dbo.Weblog( [id] [int] Identity, [host_ip] [nvarchar](16) NULL, [file] [nvarchar](255) NOT NULL, [querystring] [nvarchar](255) NULL, [timestamp] [smalldatetime] NULL ) CREATE TABLE dbo.TechStaffList( [host_ip] [nvarchar](16) NULL ) Create two tables

3. Familiar SQL Syntax INSERT INTO Weblog ([host_ip] ,[file] ,[querystring] ,[timestamp]) VALUES ('123.123.123', '/view_lesson.php' ,'url=http://www.te.org/.../lesson07.xml' ,getdate()) SELECT * from Weblog INSERT INTO TechStaffList(host_ip)VALUES ('123.123.123') Insert a row into the log List the Log Insert a row into the TechStaffList

4. So What’s the Problem? INSERT INTO Weblog ([host_ip] ,[file] ,[querystring] ,[timestamp]) VALUES ('123.123.123', '/view_lesson.php' ,'url=http://www.te.org/.../lesson07.xml' ,getdate()) The syntax requires precise specification of the tables involved (i.e.field names) A couple of issues for conversation: Change the database? Change the program All users have to have insert rights

5. Stored Procedures: ‘Methods’ that run in the Database AddWeblogEntry @Source_IP_Address='123.123.123' ,@TE_File_Requested= '/view_lesson.php' ,@Querystring= 'url=http://www.te.org/.../lesson07.xml' Might it be nice if we could use a function and parameter paradigm instead? We call such things Stored Procedures Name the function, provide params (input) Like a method in a program, a stored procedure can also return things

6. Creating a Stored Procedure Name the procedure CREATE PROCEDURE AddWeblogEntry @Source_IP_Addressnvarchar(16) ,@TE_File_Requestednvarchar(255) ,@Querystringnvarchar(255) AS BEGIN INSERT INTO Weblog ([host_ip] ,[file] ,[querystring] ,[timestamp]) VALUES (@Source_IP_Address, @TE_File_Requested, @querystring ,getdate() ) END List acceptable parameters Specify the SQL commands to be executed The SQL manager helps a lot, right-click & ‘new stored procedure’

7. You Can Do More CREATE TABLE dbo.TechWeblog( [id] [int] Identity, [host_ip] [nvarchar](16) NULL, [file] [nvarchar](255) NOT NULL, [querystring] [nvarchar](255) NULL, [timestamp] [smalldatetime] NULL ) What if you wanted to separate log entries from the technical staff into their own table? Can we let the database (in a stored procedure) handle that instead of writing more code in our C# program? First: Make a new table for tech staff entries

8. Now Create a ‘Smarter’ Proc ALTER PROCEDURE AddWeblogEntry @Source_IP_Addressnvarchar(16) ,@TE_File_Requestednvarchar(255) ,@Querystringnvarchar(255) AS BEGIN declare @IsTechStaffint -- declares a variable for use in this procedure -- In effect this asks if this address is in the list: 0 = no, >0 = yes select @IsTechStaff = count(*) from TechStaffList where host_ip=@Source_IP_Address if @IsTechStaff > 0 Begin INSERT INTO TechWeblog ([host_ip] ,[file] ,[querystring] ,[timestamp]) VALUES (@Source_IP_Address, @TE_File_Requested, @querystring ,getdate() ) end else Begin INSERT INTO Weblog ([host_ip] ,[file] ,[querystring] ,[timestamp]) VALUES (@Source_IP_Address, @TE_File_Requested, @querystring ,getdate() ) End END

9. What Result Do You Expect Here? truncate table Weblog -- this clears everything so we can start clean truncate table TechWeblog -- Note this is exactly the syntax as before, programs that CALLS the proc need NOT change exec AddWeblogEntry '123.123.121', '/view_lesson.php', 'url=http://www.te.org/.../l1.xml' exec AddWeblogEntry '123.123.122', '/view_lesson.php', 'url=http://www.te.org/.../l2.xml' exec AddWeblogEntry '123.123.123', '/view_lesson.php', 'url=http://www.te.org/.../l3.xml' exec AddWeblogEntry '123.123.124', '/view_lesson.php', 'url=http://www.te.org/.../l4.xml' exec AddWeblogEntry '123.123.121', '/view_lesson.php', 'url=http://www.te.org/.../l5.xml' select * from WebLog select * from TechWeblog

10. Even Wilder…. Triggers What if we often realize after the fact that certain IP addresses are part of the tech staff? We can have the database to perform special procedures called triggers whenever data in a table is changed (UPDATE, INSERT, or DELETE). So, this is a bit far fetched – given the frequency of changes and other issues. This example may not justify a trigger. But, lets go with it to understand HOW a trigger works.

11. Create A Trigger CREATE TRIGGER dbo.Tr_TechStaff_IPAddress_Change ON dbo.TechStaffListFOR INSERT,UPDATE,DELETE AS BEGIN SET NOCOUNT ON; -- avoids extra result sets that would be generated -- When records are Deleted or Updated, the old contents are listed in 'deleted' -- So, we will move any log records for this ip from the Tech list back to the main list INSERT INTO Weblog ([host_ip] ,[file] ,[querystring] ,[timestamp]) SELECT TechWebLog.[host_ip] ,[file] ,[querystring] ,[timestamp] from TechWebLog, deleted where TechWebLog.host_ip = deleted.host_ip Delete TechWebLog where host_ip in (select host_ip from deleted) -- When records are inserted or updated, the new contents are listed in the table 'inserted' -- So our code will 'move' all the records in WebLog to TechWebLog for these addresses INSERT INTO TechWeblog ([host_ip] ,[file] ,[querystring] ,[timestamp]) SELECT WebLog.[host_ip] ,[file] ,[querystring] ,[timestamp] from WebLog, inserted where WebLog.host_ip = inserted.host_ip Delete WebLog where host_ip in (select host_ip from inserted) END

12. What Result Do You Expect Here? truncate table Weblog ; truncate table TechWeblog; truncate table TechStaffList -- clear old stuff exec AddWeblogEntry '123.123.121', '/view_lesson.php', 'url=http://www.te.org/.../l1.xml' exec AddWeblogEntry '123.123.122', '/view_lesson.php', 'url=http://www.te.org/.../l2.xml' exec AddWeblogEntry '123.123.123', '/view_lesson.php', 'url=http://www.te.org/.../l3.xml' exec AddWeblogEntry '123.123.124', '/view_lesson.php', 'url=http://www.te.org/.../l4.xml' exec AddWeblogEntry '123.123.121', '/view_lesson.php', 'url=http://www.te.org/.../l5.xml' select * from WebLog; select * from TechWebLog; select * from TechStaffList INSERT INTO TechStaffList (host_ip)VALUES ('123.123.123') select * from WebLog; select * from TechWebLog; select * from TechStaffList INSERT INTO TechStaffList (host_ip)VALUES ('123.123.121') select * from WebLog; select * from TechWebLog; select * from TechStaffList DELETE TechStaffList where host_ip = '123.123.121' select * from WebLog; select * from TechWebLog; select * from TechStaffList

13. So – That Was a Quick Intro Now lets look at the notes on line

14. Accounts Receivable Application Extending Credit to Customers Application Architecture Production Interfaces Business Logic EDI supports efficient customer processes Processing instructions enact transactions Sales identifies new customers Policies and access controls reduce risk Credit managers adjust credit limits Web store allows direct sales Database Direct DB access through utility apps

15. Risk Number 1: Bad Credit limits • The Business Risk: If credit limits are changed inappropriately, we might ship product for which we will never be paid • Control: Only selected individuals are authorized to set or change credit limits • Control implementation • programs that change limits must check a list of authorized people before changing a limit • changes are logged for verification

16. More Risks: Errors or Hacks in a Heterogeneous Environment Can you see how stored procedures or triggers could help here? • Risk: Given the multiple interfaces that might change the limits, some one of many components may have an error that could result in wrong credit limit data • Risk: Someone could go in with a utility program or an SQL injection attack and change a limit thereby avoiding coded controls • This might be inadvertent or fraudulent

17. What needs to be done to change a customer’s credit limit? Get the user name from the system Windows handles this when it connects to the DB A database lists users in roles This role is called ChgClientCreditLimit if ( (Select count(*) where Person, Role) > 0) OK Update Clients Set CreditLimit=? , this customer Worked? Remember what was done by whom Forbidden? Remember who tried Tell the user what happened • Who am I? • May I? • Do it • Log it • Display

18. 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 ‘Blindly’ perform SQL instructions Execute and check success 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 DB Server Execute and check success Display results

19. C#.Net DB Server Scenario 2 – Stored Proc Stored Procedure ChgClientCreditLimit 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 Half the C# code but involved DB procedure code: authorization logic, logging functions, and table/column details are not included in the C# program 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

20. C#.Net DB Server Scenario 3 – Proc + Trigger Stored Procedure ChgClientCreditLimit Connect, Authenticate, Check for success Logging is moved into a trigger. Changes are logged no matter how the updates are made: code, proc, or utility In our lab, authorization is also moved to its own proc, AuthCheck ,which logs denied attempts Specify authorization parameters Specify update parameters Specify tables, columns, and SQL Exec Stored Proc  Execute and check success Display results Specify tables, columns, and SQL Execute and check success Specify logging parameters Specify tables, columns, and SQL Database Trigger Logs the Activity Execute and check success Specify Results parameters Specify tables, columns, and SQL Trigger Fires Automatically 

21. Things to Ponder Moving functionality from client, to web server, to DB code profoundly affects a variety of important issues. Which is best? IT DEPENDS • 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