2008年6月25日 11:12:28
1.检查要创建的表是否存在？
<img alt=”” src=”https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif” align=”top”><span style=”color: #0000ff”>IF
<span style=”color: #000000″><span style=”color: #ff00ff”>OBJECT_ID<span style=”color: #000000″>(<span style=”color: #ff0000″>'<span style=”color: #ff0000″>Price_history<span style=”color: #ff0000″>'<span style=”color: #000000″>,<span style=”color: #ff0000″>'<span style=”color: #ff0000″>U<span style=”color: #ff0000″>'<span style=”color: #000000″>)<span style=”color: #0000ff”>IS<span style=”color: #000000″><span style=”color: #808080″>NOT<span style=”color: #000000″><span style=”color: #0000ff”>NULL<span style=”color: #000000″>
<img alt=”” src=”https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif” align=”top”>
<span style=”color: #0000ff”>DROP<span style=”color: #000000″><span style=”color: #0000ff”>TABLE<span style=”color: #000000″>Price_history
2.复到一个表的表结构以开成一个新表。
<img alt=”” src=”https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif” align=”top”><span style=”color: #0000ff”>SELECT
<span style=”color: #000000″><span style=”font-weight: bold; color: #800000″>1<span style=”color: #000000″><span style=”color: #0000ff”>AS<span style=”color: #000000″>ProductID,UnitPrice<span style=”color: #0000ff”>AS<span style=”color: #000000″>OldPrice,UnitPrice<span style=”color: #0000ff”>AS<span style=”color: #000000″>NewPrice,<span style=”color: #ff00ff”>GETDATE<span style=”color: #000000″>()<span style=”color: #0000ff”>AS<span style=”color: #000000″>Date
<img alt=”” src=”https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif” align=”top”>
<span style=”color: #0000ff”>INTO<span style=”color: #000000″>Price_history
<img alt=”” src=”https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif” align=”top”>
<span style=”color: #0000ff”>FROM<span style=”color: #000000″>dbo.Products
<img alt=”” src=”https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif” align=”top”>
<span style=”color: #0000ff”>WHERE<span style=”color: #000000″><span style=”font-weight: bold; color: #800000″>1<span style=”color: #808080″>=<span style=”font-weight: bold; color: #800000″>0
3.判断某触发器是否存在？
<img alt=”” src=”https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif” align=”top”><span style=”color: #0000ff”>IF
<span style=”color: #000000″><span style=”color: #ff00ff”>OBJECT_ID<span style=”color: #000000″>(<span style=”color: #ff0000″>'<span style=”color: #ff0000″>trg_Products_u<span style=”color: #ff0000″>'<span style=”color: #000000″>,<span style=”color: #ff0000″>'<span style=”color: #ff0000″>TR<span style=”color: #ff0000″>'<span style=”color: #000000″>)<span style=”color: #0000ff”>IS<span style=”color: #000000″><span style=”color: #808080″>NOT<span style=”color: #000000″><span style=”color: #0000ff”>NULL<span style=”color: #000000″>
<img alt=”” src=”https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif” align=”top”>
<span style=”color: #0000ff”>DROP<span style=”color: #000000″><span style=”color: #0000ff”>TRIGGER<span style=”color: #000000″>trg_Products_u;
<img alt=”” src=”https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif” align=”top”>
<span style=”color: #0000ff”>GO
4.创建一个<font color=”#800000″>触发器
<img alt=”” src=”https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif” align=”top”><span style=”color: #0000ff”>CREATE
<span style=”color: #000000″><span style=”color: #0000ff”>TRIGGER<span style=”color: #000000″>trg_Products_u<span style=”color: #0000ff”>ON<span style=”color: #000000″>dbo.Products<span style=”color: #0000ff”>FOR<span style=”color: #000000″><span style=”color: #0000ff”>UPDATE<span style=”color: #000000″>
<img alt=”” src=”https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif” align=”top”>
<span style=”color: #0000ff”>AS<span style=”color: #000000″>
<img alt=”” src=”https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif” align=”top”>
<span style=”color: #008080″>–<span style=”color: #008080″>如果更新的不是UnitPrice或没有更新直接返回<span style=”color: #008080″>
<img alt=”” src=”https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif” align=”top”>
<span style=”color: #0000ff”>IF<span style=”color: #000000″><span style=”color: #808080″>NOT<span style=”color: #000000″><span style=”color: #0000ff”>UPDATE<span style=”color: #000000″>(UnitPrice)<span style=”color: #808080″>OR<span style=”color: #000000″><span style=”font-weight: bold; color: #008000″>@@ROWCOUNT<span style=”color: #808080″>=<span style=”font-weight: bold; color: #800000″>0<span style=”color: #000000″>
<img alt=”” src=”https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif” align=”top”>
<span style=”color: #0000ff”>RETURN<span style=”color: #000000″>;
<img alt=”” src=”https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif” align=”top”>
<span style=”color: #0000ff”>ELSE<span style=”color: #000000″>
<img alt=”” src=”https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif” align=”top”>
<span style=”color: #0000ff”>INSERT<span style=”color: #000000″><span style=”color: #0000ff”>INTO<span style=”color: #000000″>price_history//实现了在更新价格时在日志表里记录下原始价格和新价格
<img alt=”” src=”https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif” align=”top”>
<span style=”color: #0000ff”>SELECT<span style=”color: #000000″>i.ProductID,d.UnitPrice,i.UnitPrice,<span style=”color: #ff00ff”>getdate<span style=”color: #000000″>()
<img alt=”” src=”https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif” align=”top”>
<span style=”color: #0000ff”>FROM<span style=”color: #000000″>insertedi
<img alt=”” src=”https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif” align=”top”>
<span style=”color: #808080″>join<span style=”color: #000000″>deletedd //用Inserted和Deleted这两个表实现：）
<img alt=”” src=”https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif” align=”top”>
<span style=”color: #0000ff”>on<span style=”color: #000000″>i.ProductID<span style=”color: #808080″>=<span style=”color: #000000″>d.ProductID
<img alt=”” src=”https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif” align=”top”>
<span style=”color: #0000ff”>GO
5.一个带多表查询的UPdate语句
<img alt=”” src=”https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif” align=”top”><span style=”color: #0000ff”>update
<span style=”color: #000000″>p
<img alt=”” src=”https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif” align=”top”>
<span style=”color: #0000ff”>set<span style=”color: #000000″>UnitPrice<span style=”color: #808080″>=<span style=”color: #000000″>UnitPrice<span style=”color: #808080″><span style=”font-weight: bold; color: #800000″>1.5<span style=”color: #000000″>
<img alt=”” src=”https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif” align=”top”>
<span style=”color: #0000ff”>from<span style=”color: #000000″>dbo.Productsp
<img alt=”” src=”https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif” align=”top”>
<span style=”color: #808080″>join<span style=”color: #000000″>dbo.Supplierss
<img alt=”” src=”https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif” align=”top”>
<span style=”color: #0000ff”>on<span style=”color: #000000″>p.SupplierID<span style=”color: #808080″>=<span style=”color: #000000″>s.SupplierID
<img alt=”” src=”https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif” align=”top”>
<span style=”color: #0000ff”>where<span style=”color: #000000″>s.city<span style=”color: #808080″>=<span style=”color: #000000″>N<span style=”color: #ff0000″>'<span style=”color: #ff0000″>London<span style=”color: #ff0000″>’
6.检查、创建、执行存储过程
<img alt=”” src=”https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif” align=”top”><span style=”color: #0000ff”>IF
<span style=”color: #000000″><span style=”color: #ff00ff”>OBJECT_ID<span style=”color: #000000″>(<span style=”color: #ff0000″>'<span style=”color: #ff0000″>prc_UpdateProductPrice<span style=”color: #ff0000″>'<span style=”color: #000000″>,<span style=”color: #ff0000″>'<span style=”color: #ff0000″>P<span style=”color: #ff0000″>'<span style=”color: #000000″>)<span style=”color: #0000ff”>IS<span style=”color: #000000″><span style=”color: #808080″>NOT<span style=”color: #000000″><span style=”color: #0000ff”>NULL<span style=”color: #000000″>
<img alt=”” src=”https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif” align=”top”>
<span style=”color: #0000ff”>DROP<span style=”color: #000000″><span style=”color: #0000ff”>PROC<span style=”color: #000000″>prc_UpdateProductPrice;
<img alt=”” src=”https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif” align=”top”>
<span style=”color: #0000ff”>GO<span style=”color: #000000″>
<img alt=”” src=”https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif” align=”top”>
<span style=”color: #0000ff”>CREATE<span style=”color: #000000″><span style=”color: #0000ff”>PROCEDURE<span style=”color: #000000″>prc_UpdateProductPrice
<img alt=”” src=”https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif” align=”top”>
<span style=”color: #0000ff”>WITH<span style=”color: #000000″>RECOMPILE
<img alt=”” src=”https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif” align=”top”>
<span style=”color: #0000ff”>AS<span style=”color: #000000″>
<img alt=”” src=”https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif” align=”top”>
<span style=”color: #0000ff”>BEGIN<span style=”color: #000000″>
<img alt=”” src=”https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif” align=”top”>
<span style=”color: #0000ff”>SET<span style=”color: #000000″>NOCOUNT<span style=”color: #0000ff”>ON<span style=”color: #000000″>;
<img alt=”” src=”https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif” align=”top”>
<span style=”color: #0000ff”>update<span style=”color: #000000″>p
<img alt=”” src=”https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif” align=”top”>
<span style=”color: #0000ff”>set<span style=”color: #000000″>UnitPrice<span style=”color: #808080″>=<span style=”color: #000000″>UnitPrice<span style=”color: #808080″><span style=”font-weight: bold; color: #800000″>1.5<span style=”color: #000000″>
<img alt=”” src=”https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif” align=”top”>
<span style=”color: #0000ff”>from<span style=”color: #000000″>dbo.Productsp
<img alt=”” src=”https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif” align=”top”>
<span style=”color: #808080″>join<span style=”color: #000000″>dbo.Supplierss
<img alt=”” src=”https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif” align=”top”>
<span style=”color: #0000ff”>on<span style=”color: #000000″>p.SupplierID<span style=”color: #808080″>=<span style=”color: #000000″>s.SupplierID
<img alt=”” src=”https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif” align=”top”>
<span style=”color: #0000ff”>where<span style=”color: #000000″>s.city<span style=”color: #808080″>=<span style=”color: #000000″>N<span style=”color: #ff0000″>'<span style=”color: #ff0000″>London<span style=”color: #ff0000″>'<span style=”color: #000000″>
<img alt=”” src=”https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif” align=”top”>
<span style=”color: #0000ff”>END<span style=”color: #000000″>
<img alt=”” src=”https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif” align=”top”>
<span style=”color: #0000ff”>GO<span style=”color: #000000″>
<img alt=”” src=”https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif” align=”top”>
<span style=”color: #0000ff”>EXEC<span style=”color: #000000″>prc_UpdateProductPrice
<img alt=”” src=”https://www.jb51.cc/res/2019/03-05/01/a6339ee3e57d1d52bc7d02b338e15a60.gif” align=”top”>
<span style=”color: #0000ff”>GO
