Home>

Add and delete triggers

alter trigger tri_tc on t_c
  for insert, delete
as
begin
  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);
  end
end

Update triggers and transactions

Transactions are mainly used for data protection,When updating multiple tables,Transaction save update statements under all transactions will not be committed,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 a transaction
   declare @cname varchar (100);
   select @cname=cname from inserted;--Save the updated content
   update t_c2 set [email protected] 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
end

Stored procedure

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
go
exec pro_fun

cursor

declare @qybh varchar (10)
declare cur cursor for
  select distinct qybh from pj_enterpriseinput
open cur
fetch next from cur into @qybh
while @@ fetch_status=0
 begin
  print (@qybh)
  fetch next from cur into @qybh
 end
close cur
deallocate cur

view

alter view createview
as
 select qybh from createview
go

Define method

alter function funname (@ str1 varchar (10),@str2 varchar (10))
returns varchar (10)
as
begin
  declare @returnstr varchar (10)
  set @returnstr="false"
  if (@ str1>@ str2)
    set @returnstr="true"
  return @returnstr
end
select dbo.funname (..., ...)

Defining table variables

declare @qybhtable table (id varchar (32), qybh varchar (30))
insert into @qybhtable
select id, qybh from pj_enterpriseinput
select * from @qybhtable

case when then

select
sum (case when z.watchname="registered supervision engineer" then 1 else 0 end),sum (case when z.watchname="xinza" then 1 else 0 end),sum (case when z.watchname="Supervisor" then 1 else 0 end)
from zu_corjl z
right join zu_corjltemp t on t.corid=z.corid
  • Previous Android uses Vitamio to create its own universal player (10)-local playback (thumbnails, video information, video scanning servi
  • Next Fully understand the role of Native keywords in Java