SQL Server triggers and transaction usage examples _mssql

The examples in this article describe SQL Server triggers and transaction usages. Share to everyone for your reference, specific as follows:

Add and remove triggers

Alter TRIGGER TRI_TC on T_c to
  Insert,delete
as
begin
  set XACT_ABORT on
  declare @INSERTCOUNT int;< C7/>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 * inserted;
  End
  Else if (@DELETECOUNT > 0)
  begin
   Delete t_c2 where exists (select Temp.cid from deleted temp where tem P.CID=T_C2.CID);
  End End

Update triggers and transactions

Transactions are primarily used for data protection, and when multiple tables are updated, the transaction saves the update statements under all transactions without committing and the data cannot be updated successfully

Alter TRIGGER tri_tc_update on T_c for
  Update
as
begin
  declare @delcount int;
  Set @delcount = (select count (*) from deleted);
  if (@delcount > 0)
  begin
   BEGIN TRANSACTION Triupdate-Define transaction
   declare @cname varchar (m);
   Select @cname = cname from inserted; --Save 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 end

Stored Procedures

if (exists (select name from sysobjects s where s.name= ' Pro_fun ' and s.type= ' P '))
  drop procedure Pro_fun
go
  CREATE PROCEDURE Pro_fun
as
  select * from table
to
exec pro_fun

Cursor

DECLARE @qybh varchar (a)
declare cur cursor for
  select DISTINCT QYBH from pj_enterpriseinput
Open cur
FETCH NEXT from cur to @qybh while
@ @fetch_status = 0
 begin
  Print (@qybh)
  fetch NEXT from cur into @qybh C9/>end close
cur
deallocate cur

View

Alter VIEW CreateView
as
 select Qybh from CreateView
go

Defining methods

Alter function funname (@str1 varchar), @str2 varchar)
returns varchar (a) as
begin
  DECLARE @returnStr varchar (a)
  set @returnStr = ' false '
  if (@str1 > @str2)
    Set @returnStr = ' true ' return
  @returnStr
The End Select Dbo.funname (...)

Defining table Variables

Declare @qybhTable table (ID varchar (), QYBH varchar)
INSERT INTO @qybhTable
select Id,qybh from Pj_ Enterpriseinput
SELECT * from @qybhTable

Use when then condition statistics in case

Select
sum (case when Z.watchname= ' registered Supervisor Engineer ' then 1 else 0 end),
sum (case when z.watchname= ' Xinza ' then 1 else 0 end ),
sum (case when z.watchname= ' Inspector ' then 1 else 0) from
zu_corjl Z-right
join Zu_corjltemp T on T.corid=z . Corid

I hope this article will help you with your SQL Server database program.