SQL Server trigger and transaction usage example, server trigger

SQL Server trigger and transaction usage example, server trigger

This example describes SQL Server triggers and transaction usage. We will share this with you for your reference. The details are as follows:

Add and delete triggers

alter trigger tri_TC on t_c  for INSERT,deleteasbegin  set XACT_ABORT ON  declare @INSERTCOUNT int;  declare @DELETECOUNT int;  declare @UPDATECOUNT int;  set @INSERTCOUNT = (select COUNT(*) from inserted);  set @DELETECOUNT = (select COUNT(*) from deleted);  set @UPDATECOUNT = ()  if(@INSERTCOUNT > 0)  begin   insert into t_c2 select * from inserted;  end  else if(@DELETECOUNT > 0)  begin   delete t_c2 where exists(select temp.cid from deleted temp where temp.cid=t_c2.cid);  endend

Update triggers and transactions

Transactions are mainly used to protect data. When multiple tables are updated, the update statements of all transactions stored in the transaction will not be submitted, and the data cannot be updated successfully.

Alter trigger tri_TC_Update on t_c for updateasbegin declare @ delcount int; set @ delcount = (select count (*) from deleted); if (@ delcount> 0) begin transaction triUpdate -- Define the transaction declare @ cname varchar (100); select @ cname = cname from inserted; -- save the updated content update t_c2 set cname = @ cname where cid = (select cid from deleted); -- update if (@ error <> 0) begin rollback transaction triUpdate; -- transaction rollback end else begin commit transaction triUpdate; -- transaction commit end endend

Stored Procedure

if(exists(select name from sysobjects s where s.name='pro_fun' and s.type='p'))  drop procedure pro_fungo  create procedure pro_funas  select * from tablegoexec pro_fun

Cursor

declare @qybh varchar(10)declare cur cursor for  select distinct qybh from PJ_EnterpriseInputopen curfetch next from cur into @qybhwhile @@fetch_status = 0 begin  print(@qybh)  fetch next from cur into @qybh endclose curdeallocate cur

View

alter view CreateViewas select qybh from CreateViewgo

Definition Method

alter function funName(@str1 varchar(10),@str2 varchar(10))returns varchar(10)asbegin  declare @returnStr varchar(10)  set @returnStr = 'false'  if(@str1 > @str2)    set @returnStr = 'true'  return @returnStrendselect dbo.funName(... , ...)

Define table Variables

declare @qybhTable table (id varchar(32),qybh varchar(30))insert into @qybhTableselect id,qybh from PJ_EnterpriseInputselect * from @qybhTable

Use case when then Condition statistics

Selectsum (case when z. watchName = 'registered supervision engineer' then 1 else 0 end), sum (case when z. watchName = 'interna' then 1 else 0 end), sum (case when z. watchName = 'authorization' then 1 else 0 end) from zu_corjl zright join zu_corjltemp t on t. corID = z. corID

I hope this article will help you design SQL Server database programs.