SQL Server review notes 06

-- 12. stored Procedure -- create procedure book1_pr1asselect book1. name from book1 -- execute the Stored Procedure execute book1_pr1 -- view the Stored Procedure sp_helptext book1_pr1 -- create proc book1_pr2 @ name char (20) asselect * From book1where Title = @ name -- execute the stored procedure with parameters exec book‑pr2 @ name = 'SQL database' -- stored procedure with output parameters, specify the output keyword create proc book1_pr3 @ title char (20), @ book1num smallint outputasset @ book1num = (select count (*) from book1where Title = @ title) print @ book1num -- execution time The input parameter must be assigned a value. The output parameter does not need to be assigned a value. Declare @ name: Char (20), @ book1num smallintset @ name = 'SQL database' exec book1_pr3 @ name, @ book1num -- modify, encrypt and re-compile the Stored Procedure alter proc book‑pr1 @ press varchar (20) /* with encryption */with recompile -- Re-compile asselect * From book1where press = @ dedeclare @ varchar (20) during definition) set @ press = 'Sun Yat-sen 'exec book1_pr1 @ press -- Re-compile declare @ press varchar (20) during execution) set @ press = 'Sun Yat-sen 'exec book1_pr1 @ press with recompile -- recompile exec sp_reco through the system stored procedure Mpile book1_pr1 -- thirteen. trigger, insert/delete/update -- create trigger book1_tr1on book1for insert -- trigger and trigger statement will execute asprint 'insert into book1 values ('20140901', '20160301 ', 'SQL database', 35, 'Sun Yat-sen University', '03-13-2008 ') -- trigger execution, but the trigger statement is not executed. Instead ofcreate trigger book1_tr2on book1instead of deleteasprint cannot be deleted! 'Delete from book1 -- Test select * From book1 -- The data in the table is not deleted -- If update (column_name), no instead, instead, use rollback transaction to roll back the create trigger book1_tr3on book1for updateasif Update (pricing) beginrollback transactionend -- Test update book1set pricing = 45 where no. = '2016'/* message 101172, level 16, status 1, 1st rows of transactions ended in the trigger. Batch processing has been aborted. */-- Manage the trigger -- 1. sp_help trigger_name: understand the general information of the trigger, such as name and creation time. Exec sp_help book1_tr3 -- 2, sp_helptext trigger_name: view the trigger definition information exec sp_helptext book1_tr3 -- 3, sp_depends trigger_name |, view all the triggers and stored procedures involved in the table or table referenced by the trigger exec sp_depends book1_tr3exec sp_depends book1 -- 4, sp_helptrigger table_name, view the information of all the triggers involved in the table exec sp_helptrigger book1 -- 5, view trigger information through the system table select * From sysobjectswhere type = 'tr' -- 6, disable trigger alter table book1disable trigger trigger_name | all -- enable trigger alter table book1enable trigger trigger_name | all

Learning material download