The article directories

• The stored procedure
• • Creation and Execution
  • Modify and delete
• The trigger
• • create
  • Modify and delete
• summary

Learning makes me happy

The stored procedure

Stored Procedure is a database object that stores complex programs in a database for external programs to call. In SQL Server, stored procedures are classified into two types: system-provided stored procedures and user-defined stored procedures. The former is prefixed with sp_ and gets information mainly from system tables. The latter is written by the user using t-SQL.

Creation and Execution

CREATE { PROC | PROCEDURE} [schema name.] Procedure name [; group number]/* Define the procedure name */[{@ parameter [type schema name.] Data type}/* Defines the type of the argument */
        [ VARYING ] [ = default ] [ OUT | OUTPUT ] [READONLY]	/* Defines the attributes of the parameter */
    ]
 [ WITH  ENCRYPTION ]	/* Indicates whether to use the encryption mode */
 [ FOR REPLICATION ]/* Indicates that the stored procedure created for replication cannot be executed at the subscriber. * /
AS
{	 <SQLstatements>		/* The operation performed */... }Copy the code

[{EXEC | EXECUTE}] {[@ returns status=] {module name|@ module name variable} [[@ parameter name=] {value|@ variable [OUTPUT]| [ DEFAULT]]}}Copy the code

create procedure p1
as
	select * from s,p,j,spj
	where s.sno=spj.sno
	and p.pno=spj.pno
	and j.jno=spj.jno
go
execute p1
Copy the code

create procedure p2 
@sno char(3),-- Input parameter
@city varchar(10) output-- Output parameter
as
	select @city=city from s where sno=@sno
go
declare @rlt varchar(10)-- There are RLT variables for output parameters
execute p2 's2'.@rlt outputQuery the city where S2 resides
select @rlt
Copy the code

create procedure p3 
@pno char(3),
@pname varchar(10),
@color char(2)='black'.- the default values
@weight int =10
as
	insert into p
	values(@pno.@pname.@color.@weight)
go
execute p3 'p7'.'screw'
execute p3 'p8'.'screw'.'silver'
execute p3 'p9'.'Screwdriver'.default.15

select *from p;
Copy the code

create procedure p4
with encryption
as
	select * from j
go 
execute sp_helptext p4
/* Sp_helptext displays the text of rules, default values, unencrypted stored procedures, user-defined functions, triggers, or views through system stored procedure sp_helptext. * /
Copy the code

create procedure p5
as
	declare @cnt int;
	select @cnt=count(jno) from j
	return @cnt
go
declare @rlt int;
set @rlt=0;
execute @rlt=p5
select @rlt
Copy the code

Modify and delete

1. Syntax format ① modification: the above create can be changed into ALTER, not repeated. (2) to delete

DROP { PROC | PROCEDURE} {[schema name.] Process} [,...]Copy the code

2. Example 1 Change stored procedure P3 to query table P.

alter procedure p3
as
	select *from p
go
execute p3 ;
Copy the code

② Delete procedure P3

drop procedure p3
Copy the code

The trigger

create

1. Syntax format DML triggers

CREATE TRIGGER[Schema name.] Trigger nameON{table|View}/* Specifies the operation object */
[ WITH  ENCRYPTION ]			/* Indicates whether to use the encryption mode */
{ FOR | AFTER | INSTEAD OF} {[INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE]} [WITH APPEND ]	  /* Specifies that another trigger of an existing type should be added */
[ NOT FOR REPLICATION ]		/* Indicates that the trigger is not used for replication */
AS
{
    <SQLstatements>... }Copy the code

DDL trigger

CREATE TRIGGERThe trigger nameON { ALL SERVER | DATABASE }
[ WITH ENCRYPTION ]
{ FOR |AFTER} {Event type|Event group} [,...]AS
{
    SQLStatement [;] [...].| EXTERNALNAME Assembly NAME. Class name. Method name}Copy the code

1. Create insert trigger T1 to replace the insert operation when the SPJ table inserts records, check the integrity, and execute corresponding statements.

create trigger t1 on spj
instead of insert
as 
	declare @sno char(3), @pno char(3),@jno char(3)
	declare @qty int
	select @sno=sno from inserted- assignment
	select @pno=pno from inserted
	select @jno=jno from inserted
	select @qty=qty from inserted
	if(exists (select * from spj where sno=@sno and pno=@pno and @jno=@jno)-- Entity integrity
	or not exists (select * from s where sno=@sno)-- Referential integrity
	or not exists (select * from p where pno=@pno)
	or not exists (select * from j where jno=@jno))
		print 'Insert failed'
	else 
		begin
		insert into spj values(@sno.@pno.@jno.@qty)
		print'Inserted successfully'
		end
go
insert into spj values('s1'.'p1'.'j1'.100)
insert into spj values('s9'.'p2'.'j1'.200)
insert into spj values('s1'.'p3'.'j9'.300)
insert into spj values('s1'.'p4'.'j3'.400)
Copy the code

(2) to createdeleteTrigger T2, after the SPJ table deletes records, displays the number of remaining records.

create trigger t2 on spj
after delete
as 
	declare @cnt int
	select @cnt=count(sno) from spj
	select @cnt as 'Total number of remaining Records'
go
delete spj where sno='s1' and pno='p4' and jno='j3'
Copy the code

(3) to createupdateTrigger T3 displays the old value of the updated attribute before table S is updated.

create trigger t3 on s 
for update
as
	select * from deleted
go
update s set status=25, city='xiamen' where sno='s4'
select *from s
Copy the code

(4) Create DDL trigger T4 in SPJ database scope. When a table is deleted, a message is displayed indicating that the operation is forbidden and the database deletion operation is rolled back.

create trigger t4 
on database
after DROP_TABLE
as
	print'This table cannot be deleted'
	rollback transaction
go
drop table spj
Copy the code

Modify and delete

DROP TRIGGERSchema name. Trigger name [,... [;]/* Delete the DML trigger */
DROP TRIGGERTrigger name [,...]ON { DATABASE | ALL SERVER }[ ; ]	/* Delete DDL trigger */
Copy the code

alter trigger t3 on s 
after update
as
	select * from s
go
update s set status=20, city='tianjin' where sno='s4'
Copy the code

drop trigger t3
drop trigger t4 on database
Copy the code

summary

1. Stored procedure 1 The stored procedure runs quickly on the server. Once executed, it resides in cache memory, improving system performance. ② You can use stored procedures to complete all database operations and control access permissions to the database to ensure database security.
2. Trigger ① can implement more complex statements than the CHECK constraint and easily ensure database integrity. ② Triggers cascade changes/deletes through related tables in the database.
3. The proper use of stored procedures and triggers can reduce code redundancy, but too much can complicate data logic.

Original is not easy, please do not reprint (this is not rich visits add insult to injury) blogger home page: blog.csdn.net/qq_45034708 If the article is helpful to you, remember to focus on the likes collection ❤