SQL not common function summary and transaction, add, delete trigger _mssql

Source: Internet
Author: User
Tags commit rollback
SQL not common function summary and transaction, add, delete trigger

Distinct delete duplicate rows

Declare @x declares a variable

CONVERT (varchar (), tscore.tenglish) type conversion

Cast (xx as varchar (10)) type conversion
Www.jb51.net
======================================================

Situation
Case
When xxx then XX
When XXXX then xxxxx
Else
End

=======================================================

While xxx
Begin
Xxxxxx
End
Www.jb51.net
Transaction
===================================================
BEGIN Tran
declare @sum int = 0
Update bank set balance=balance-1000 where cid= ' 0001 '
Set @sum = @sum +@ @error
Update Bank set balance=balance + 1000 where cid= ' 0002 '
Set @sum = @sum +@ @error

If @sum <>0
Begin
Rollback
print ' Roll back! '
End
Else
Begin
--Submitting a transaction
Commit Tran
print ' Submitted! '
End
===================================================
BEGIN Tran Www.jb51.net
Begin try
declare @sum int = 0
Update bank set balance=balance-900 where cid= ' 0001 '
Set @sum = @sum +@ @error
Update Bank set balance=balance + 900 where cid= ' 0002 '
Set @sum = @sum +@ @error
Commit
End Try
Begin Catch
Rollback
End Catch
=====================================================

DECLARE @halfCount int= Ceiling (@count/2.0) up

Trigger when added
=========================================
Create Trigger Tri_tblclass_insert_after
On Tblclass after insert
As
Begin
DECLARE @id int
DECLARE @name varchar (50)
DECLARE @desc varchar (50)
Select @id =tclassid, @name =tclassname, @desc =tclassdesc from inserted

Print @id
Print @name
Print @desc
End Www.jb51.net
=========================================

triggers when deleting
Create Trigger Tri_tblclass_delete_after
On Tblclass instead of delete
As
Begin

INSERT INTO Tblclassbak
SELECT * from deleted
End
========================================


Author xhccom

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.