简介

触发器是当特定事件出现时自动执行的存储过程，特定事件可以是执行更新的DML语句和DDL语句，触发器不能被显式调用。
 
触发器的功能：
1.自动生成数据
2.自定义复杂的安全权限
3.提供审计和日志记录
4.启用复杂的业务逻辑
 

创建触发器的语法

CREATE [OR REPLACE] TRIGGER trigger_nameAFTER | BEFORE | INSTEAD OF[INSERT] [[OR] UPDATE [OF column_list]] [[OR] DELETE]ON table_or_view_name[REFERENCING {OLD [AS] old / NEW [AS] new}][FOR EACH ROW][WHEN ( condition ) ]pl/sql_block;

:new --为一个引用最新的列值;
:old --为一个引用以前的列值;这两个变量只有在使用了关键字"FOREACH ROW"时才存在.

且update语句两个都有,而insert只有:new,delect 只有:old;

 

触发器由三部分组成：

触发器语句（事件）
　　定义激活触发器的 DML 事件和 DDL 事件
触发器限制
　　执行触发器的条件，该条件必须为真才能激活触发器
触发器操作（主体）
　　包含一些 SQL 语句和代码，它们在发出了触发器语句且触发限制的值为真时运行
 

触发器语句

CREATE OR REPLACE TRIGGER trig_sal  AFTER UPDATE OF empsal ON salary_records --在更新 emp_sal 列之后激活触发器  …

触发器限制

    …  FOR EACH ROW  WHEN (NEW.empsal>OLD.empsal)  --只有在WHEN子句中的条件得到满足时，才激活trig_sal 触发器  DECLARE  Sal_diff NUMBER;  …  

 

触发器操作

    …  BEGIN   sal_diff:=:NEW.empsal-:OLD.empsal;  --如果WHEN子句中的条件得到满足，将执行BEGIN 块中的代码   DBMS_OUTPUT.PUT_LINE(‘工资差额：’sal_diff);  END;

 

 

BEFORE 触发器的工作原理

web1iCVwAAAADABhlma5KSmLh49uzxCC2ePZsbG2tcgIp47enq7NK0d7YrtEq5Vinv7tRscHTkxsZqlLKmioLK5OiC25cyLh1OOO4WtX9V+I4lnstmTR7/u+d+/jPH/06LPXdUUpihbmu28cCL3p6ebo26W6PsbJdpFVKtvF7bWqtprlJLy9sbStslJar6EmVtYUlanKquQFVX0C4p6ZAIOxpKNU2i7PuxwRf8zvkcOe977MxR7+S78TbeWQAAAAAYFobNmkSHh9vNmGHBlGCMe7u7N61epVW0qmWNBtK0NctbWxfPnp2fk6Nsrm0oyiq7H5Efyc68cjTp1K74w1/fO+qa4rfnxsFtO1YtXfDuW567tg9Xyx8f3Z2abo2qs71V2ybVyuo0zWK1tLxDIlTVFrbX5Ktq8pRVfGUVPz3mmrIqW1mVnRFzPey8z0nGrhPMXdzQK9UFPE2LWCuXdKqau9XK3u6u0e4QAAAAADx2hsGapCQmzp4yZTxCTg4OptfV6+3t0rRrFC1qWePaz5aoZY3q1oaO5jqVtErVUKWqLyfqVMnlMpndjBm5WZlyiag+P63sfnhhVCD/+smsK6zsIJ/csIDypEhJYUa7rAH39mKMbSTYwuR4Rk93V7emo6tDrm2TamX1mqaqjoYyVX1xe02+qlqgEmUpKtIVFem1/Hu1/Hvkb0VFelNRsqIiU1mV3V6Tq6wt6JAINU0irby+S9narVH1do+BgF8AAAAAGApDsialxcVODg5kpMRkltLe3t4udbumrZkMjXQ01238YlldUYaiushYylphb3eXXCZbbmeX9uB+W32lpCBDxIsriQ8uig0qSwyvzkxoqihQy5uIL8EYa9TqMA5nKF0YFkxmx+/Waro1Sq2yWSuv17SIOxrKVLWFqpo8ZVW2oiKD8iL8+GDqb0VFenFSRC3/XnFShKIiQyXKVtXkquqL1U2VWnlDZ7usW9Mx8r0DAAAAgJFkkNZELpN5uLuPR2jmpEkm5wNjjLvU7Rp5ExkjaW+oUtSUKKqLaJ8vSYrgyCtzZRX81rKs1rKslmIeJZWkktT1ZjAO76M31ZQ1l+fV5aZK8tOaynIVElFnh9K4JdtptFEcPtGo1cZH7xsykWnbpJrWGnVjuaquqL0mTynK1jciior0tNtX9H1JRVoMZVmKkyKUosz2mtwOiVDTLNa2Sbs0yjExUxoAAAAABs1grAk3Nva1CROef+opc9Ntero6SUBJR3O9qr6CDIrIRXmtZZnfOi45tW9bY36SSZWnRmvkjWQn+Xy+61dfHXTfUZGXqWqu0ypl5n6VyUCLuQCXx43JZ1jdnZpujbJT1ayVSzTNVe0NQlVtgaIqx9iXUF5EUZEe4n/EoED8Nb+oSydUtfkdDaUaWW1ne2tPp3bk+wgAAAAAI0Y/1sQgikIuk5FUJXYzZpicv9rb29vVoVTLGjtaGpT15YrqorbqQll5dnNxKjEfjI3OW1cta8i5J8m8U58RW58RW8uLrEuNrEuLrs+IzYu9Fh94qlv76PlIaXEx28/P38enpqrKZAt9vbzCOByNWr3B0VE/gcqIYTxo1NvT3a1p79ZFmdSqpeWquiJVda7+cxyipJAL+lEmJ+hbDQqQ7SEBx0LPn9I0iToVTV1qFca9I9xHAAAAABgx+h81yeTxwjicMA4nNSmJDJaYW7qlp6tT09asbm1QNYgU1UUKcWFrWVZjwYPG/CRpbqIk805dWvT3X6/+/P13q5NCzSnsxIGQ097GAyQm4zkwxvl8/uIPPyR/+/v4vDZhgrmsbo+DfD7fOAa2p0vbrVF2qVq18gZNs7hdIlTV5CurDB/lKCrSo9i+1N9BJzyLkyKMyygq0hUVGfUFKacO7IkLvdalVvTAVB0AAADgycXaBzoeu3ePR+iVP/zh+uXLJgt0qVVqWWN7c62ipkTflEiy4mp5UZTzuOy58x+/fVoUd1UUd6XyzqWKmIsVMRcrYtiiuCuiuCuiu0FVCdcD6FsCWZ4G+yeDNHKZzNij/O+DD6iBltLiYrJYz8gkJTN+mtPb29Ot7ehSt2nbmrSyenVTZXt9iaomTykyMWSi70W8d3xjxpekKyrSlaLs9vri2pIcrz27rl04NwJdAwAAAIBRwSprsp1GI/nms9PTnRwcZk6axPbzoyxCb29vp0quljWSwRJ5haCpILkx774kK64mObw6KbT6fojo7rXKO5fLo87fO/P9eIRifOllkWctaNsyu/NeDP02UDOAjD2HN4ORGH2rq0NJzdzhsNnTJk50dXauFomGcHL6QaNWG89L6u3u7NG0d7a3atoaNC3VHQ3lqtpCZRXf2G2wj+zTf2nZmigqMlW1+ZpmsUbRKCot2uvmZgtTkwAAAABg2Onfmrg6Oz//1FNsPz9qSz6f7+Tg8NqECWw/v97eXq2itaOlQVlXqhAXtggzyEgJMSVVXE5FDJsyHKUR/qVhp//x9K8POC8pCfEtCT5aEny0JPh4SYgvUWnY6dKw02W3/Moizy6a9nrk5bPUQalfYm5srEFAST6fv2nlCnVrg1YpozJ/aNRq8nxnuZ0dh81+HDEoJqYm9fZ2a9XdakWnsqlTXq9pruqQCFW1BcYTcxQV6X4eu/RHUOKv+Vm0JumqmrwOaZlWLunRKHFvbz6f7+/jY256FAAAAACMUfqxJnvd3F6bMMHkJJR8Pt9p8eIPJ0/m3gpR1JTIK3ObCpIbBAm1vMjqpFDRPU551HliR4QhJ0qCjxVf8y4K8ioK8pr375fm/fsl8rc5FV/zzrx4YParE6Ov6Z4fcdhsapzGONhl5qR/NVXktTfXquVNXT9O/sFhs6k0tWw/v2FM9x4dHm6wpae7q1vT3tXeFwDbWKGqK1LVCIx9RtrtK/pe5AR9a1NRsmVroqzK7pAItfL6ro42KhYnn8+H4RMAAADgScKSNeGw2bOnTDH3TKS3p1vT1sK9dfOD1yd9vdS+IjVGkhVX/SCsKuF6RfTFssizwtCTJcFHKbdREHigIPD7PPb+7/73/niEEo9tyQ3YmRuwM+8iPe/C3rwLe/PZ+wsCvy8I/L7wykFSJcF356vjnnkYH00aQ7WEw2YbOAxXZ+eoq+fbxAWqunJ1a0OnSm6wYG9pcfFeN7dpEyeam/A8UKQSifGIRU+ntluj6lK1aOUNmhZxR0Opqq7A5NMc/SETRUW6x+b1ln2JoiJdKcpS1RZrWms6Va2QtB4AAAB4UrFkTfa6uZkbY9A9x2muU1QXtQozWDu/tZs2+e4FVmV8IDElRde8i4K8Cq945rH38c9uzznjRunmnpXjEdq5+D39jcYSnN2Ze273mW+XTf7zH1qkDfrWRCqR7HVz029PdHj4ppUrGgseyMqzFTUl7Y3Vmrbm7seZAsR4HlBvb0+3Vt1NAmDl9erGynZJSXtNvrIvJ72+DOYJW2NNFBUZytoCTbO4S9Xa3al5fF0DAAAAgFFkUNlge3u1SllHc72ipqRFmCHNu1/78FbBbfbSmdPOfLe+JJhVFORVcOn73HO7sk9vyfSlZfhsSD/qnH7UmXfkq1SvlaleK9957tlXnvl1kocjeZl25CtSIP3Yugwflwwfl0xfWtbJTTmnt+SccXN+/83Ppr/p7+Oj75OcHBz0Q1DlMtnL48ZJsu5K8+63FPPaqvJUkkp1a0OXpt2g7Rq12tw85AFhwpp0d3VpVF26xfz60pnUCIzTmRQnRejHwKbdvmKcbM10uEltgbqpslPRBBnrAQAAgCeVwViTrg5lR0uDoqaktTST+JKqhOCy2wHCsJMB25x2L58nOLsz4wQt7ega4jyMdXz1B+MROr76A3MFKPEOr0rxWj31z+M+fetN/TakJCYunj1bf8vsKVPucS7UJIc3ZN9rLHjQWpGjrCvtaGno6lAZtN/0GoQDweTTnEcZYNskmmZxu+5pjmEGWDI3R3/a8An6Vv0V/ixZE7GgQ1quaZP2aFTUdCQAAAAAeJIwtCb9jih0a9VqWaOyrlRWwW/Mu1+XGqnzJSEnCoO88s7vTvRa4zr3Pze22Vv2HO889+w7zz3brzUhurHN/vlf/LyCn6rfkpmTJukHou51c/PcvrXyzuXq+yH1GTGNefdbyzIVNSUdzfWd7QryQ07GXbixsUMcODEOgO3t6e7SdnSrFdq2Jq28Tu9pjom5OVtWfDbwpznpiop0RVVOu0SolTV0qRW9vT0m2wbYJn5nTnodZIx2KwBgDMDj8eBi+YkzMGvS29Otlje1SyrbqvKa8h/UpUWLE2+U3Q4oCfEtvHKQ77cjnbWWmIl9Du96O/7XgtuwfuAk1WtlxE6HNbP+ZffaxJ6uTqoxvl5es6dMoV5Gh4cv/vDDskj/8qjzVQnBtQ9vSfPut5Zl6dyJSo4xJlOI5TKZN4MxlLNm6mkOSWci07Y1/Dg5vaG9qOXf07cmTUXJ1lsTlSirvb5EK6/vUrfBOn9jC2enz2e8+ZtJrzzPG/KgHQA82bDZ7HG/+wWDvnW0GwKMGobWxHL+j06VvKO5rq26sLnoYX1GrDjxRnnUeWHIicIrnjln3HjeTvp+Imjzp45vvXSX8bk5w/He38frR5z0q3eeezZg32aqMdUi0XiEqAGMapHo+aeeKosLEoacKIs8K7p7zdCdtCuo8BQPd/ehZDoxtibdWk2PWtmpau6USzTNurk5KlNzc0L8j+hHlkSxfQ1yr1mQUpTZXl+kldV0tssgXf3Ywtnpczbzj8Whf50x5U/uOzfLhm8SOwA8YbDZbOfFf2ZsfAHcyU8WQ2tiIX2q7lFObUlrWVYDn1udFFoRc1EYdqrwykFjX0J0l/H5kv+8+MOaj8w9pnnu5z9b+94kK60Je+P8V575tbK5gWrStIkT7WbM0H95y+9w4RXPkuDjZbf8RXeDKHeirC3paGmoq6oggSZSiWQ7jTa4U5aSmGiQBLa3p6db29GtbtMqmrTyek2TqF1S0l5bYHLdnB1rHPUjS3ascTS/dI6RNanIaK8t0LRUw/zhMYez03I2cwLO/hfO/pfX5r9N+ueE2FFaKxsAbBw2m+28+Dmc/S/Gxn8w6NtGuznAKGBoTcxmF+3t1bQ1qxpEclFeY9792pQIUfzVslt+xde8BWd3UFNvjJXk4bjotRfWvjfJ5OiIx7KZz/38Z0GbP7XSnSx67QXPDcupRpFlkCmj4OTgcGjbJr7/d4VXPEuCWWW3/Ku4nNqHt0jcibK2VN3acPf2LVLY18trcKlU/X18DB579c3NkWvbpBpZHcm01l6TpxRlGtsL2tJF+i8XTZ9mbaBJRbqiIkNZk69pFne1t3Z3Pcap0cCw4+y0nM18AWe/SSSJf83u/b86fm4vGfG1sgHAxmGz2c6LnydXCmPjP8Gd/AQxtCbmlu3tUrd3tDQoqouai1PrM+6IE2+URZ4tvn4078LejGPr+7UUtNmvv/f38dF7PjN+a+17k96eMO7M2o/3ObzrvuitsxvmUvJ2/K/rnMnb5k85u2EuqRux0+H1Z59uEQup1o5HiBr/8HB3X7tkcQZr3SN3Enm2isup5UU25t2XVfBV9eWxIcEkYEWjVi+ePXsQ8bDGcSo9nZpuNZk2/GjdHHOBJjvWOOq/XDHvw4FYk3RVbYGmSdSpbOqB1CZjCmenL9jMiTjnbX1xvF6a9M+/sC/Ceo0A8Ag2m+3s8AJ1mTC++ReD/t1oNwoYUQyticn40N6eHrW8SVlfLqvgS3MTqx+EVcayhWGnCgI9s0+5Wjng4Tpn8otP/dLkAMna9yb1O6PHf/0c4k7WvjfpwBoH0jASbjJt4kTyku3nN3PSpLSjzo/cyY3jZZFnqxKu16VFN+U/kIvyqnLTE6MjSa7Y6PBwg9Rt1mCQT7ZXb90crVyiba5qbxCqavNNThs2CDQJ8T9ikHut/2c6NXnqpkqtQtqjhdQmYwlnpy/YzFdwznsGkiVNd17y0ox33igekbWyAcD2YbPZzg4T9S8TxjevgTv5SWFoTVydnY0LPRoyKXpYnxEjuscpu+VfzDmce24XNSXHGpHHN+ZCTyzI2/G/1POg6D2fvfLMr6mBk5fHjRuPEIkgyeTxxiOUcXprqtfKBI/Vyce/LbziWXLTt+z2OXHijfqMmKbCFIW48PLJY1qlnEwndnV2HlCaE6lEYrCCj27dnI42bVujVl6nbiLThk0v6WcQWbJlxWdpt68MbNRELFBLK7Tyhm6NYb4WwJZxdvqC/f2/Mf9jk+IFvj3ltb96HfxePRz5AI159U1/hJigMaEpU/wfx3dgDMFms50dXjK4RhjfTB6KO4FLYAxpyhT/H1kTuUxmwpr0RZmQRCY1yeGVsWxhyInCQI+sU5sGajJc50x+7uc/O7thrvVVzm6YazCmsui1FwJ2rSGtWzx79niEPNzdMcZSiWQ8Qjc9da0KdFt8bdfKwiAvYdjJiugL1Umhkqy4lpI0b/etsjoRScUml8kWz55t/WydlMREgwiVni5tj0bV2d6qbWvQttZaDjT57IOZ+i8/eO3VAfkSRUW6UszvkJZq2yQ9YE3GFM5OX7APTMYCO3NSp3/ivv61KZNffhyzixFivpOF383C07Pw9Cw8IwvPzMbvZeP3s/EH2Xh2Nv4oG8/JxvNy8Cc52C4Hf5qDF+Zg+xy8mI8d+PgzPl7Gx18IsKMArxDgVbnYKRc75+K1uXh9HnbJw7Q8/E0+/jYfb87HbgX4uwK8oxDvKsS7i/DeIryvCDOL8YFi7FmCD5Xgw0J8RIhZpfh4KT5Rhn8ow2fKsX8FDqjA5yvxxUp8SYQDRfhqFb5Wha+L8Q0xDqnGYTX4Vi2+XYuj63BsHY6rx3fr8T0JTmjA9xvwAylOkeKHjZjXiNObcGYzzm7GOS1Y0IJzW3F+Ky6Q4SIZLpZjoRyXtuGyNlyuwJUKXKXEYiWuVuEaFa5tx3XtuL4dSzpwQweWqnGjGjepcbMGt2hwqwbLtFiuxW1a3NaJFZ1Y2YlVXVjVhdu7cHsX7ujC6m6s7saabqzpxtoerO3BnT24swd3EfXi7j719Km3F/fiRyIf1rB/AcYWbDbbeck/ja8Rxqb/MOjbB7dPuATG1iXwI2tSLRIthd4yNgAAIABJREFUt7Mz+ETJxBxFTUlzcaokK64qIXhwQyaUlvznxRef+mXETgdrCkfv+Wzb/CkGG9kb53/08gSSqmQ7jUZWFSatHY/Qye/WUSWPfjXX/bMPi64dLg0/XRnLrn4Q1iBISLh58drpY+rWBrISTSaP5+TgYO014+dn4GO6tepujbJT2fLjQBMT+ekNIkuSQi4MNNBEUZGuqMrpkJRq2xq61QpICDuGcHZyZB+YigX/s6zi8I9nTP2b2+aNwzu7GCHme1n4R8rG/4X7sq3el4fxox+LsNls5yWvmrxAGJumDc6dwCUwti6BH1mTTB7PIPs7xlirlLc3VrdV5TXmJ9U+vFUZFygM+6Ho6qGc026ph1cNwpokeTi+89yzU/70O2symqye/orJYu889+yDqycxxmw/v/EIvTxuHGnttIkTv/9ar1WHV/lvtHeY+q+M88zSW36iuKu1KRGNefe3r12pqq/QyJtI0Ik3g2HlisQGsTh9S/optIpGrVyieRRoYjqjiX52NY/N6wcaaKKoSFdWZbc3CLXyhi61wmB1ZcCWcXZyZB94G+cus0Y+O6dNeuX5YZxdjBBzdjY2p4/gvmxj9+Xh+tzHKGw223nJJHNXB2PTOwz6joHuEy6BsXUJ/MiapCQmUiGlhN6ebrWsUVVf3lqqy2VSdvtcyY3j+ZcYmSc2DsKXEEXsdHjlmV87vvWS5WIey2aaC0zZNn/KpvnvYIy5sbHjERqPUGlxMcbYbsaMbat+nOTt8Oqg7cs//Offbntvo0JinRbMaRMXtDdWa5Vy0lMnBwfj9PPGGFiTnu6ubm1HV0ebVtGoldWrGyvb60vaa/JNZjTZR/tKPwZ20fRpAw00UVSkK0XZ7ZKSTll9VwdYk7GEs5Mj22MmzltppSQJn9l98E8H+0+GZXYxQsx52diyPsnG83PwfLgv28B9eeif+JiGzWY7L3nNwtXB+HbGQN0JXAJj6xL4kTXhsNnPP/WU/pYuTbu6lQqAja1KuF4Webb4mnfe+d3px/ufM2xB7ovespyoPsnD0XXOZAvm5vVnn24TF5UWFxNrEsbhYIw3ODo62c0xKJx+1DnB++tP33z19NavyqPOVyeFfrN0oTA5SlFT0tHS0KVuxxjLZTK7GTNK+5slYTCjp6ers1uj6uqQPcpoUl/cXpOnFGUZu4oV8z7kxweTv5uKkv/9pz8O+GlORbpSlN1RX6KV1XWp23q7IVf9mMHZyZHt+V+cv2ZACj8xZ9I/n2dfPD/EoyPEXJSDrZd9DrbPwf+D+zJYk9GAzWY7f/aG5UuD8e2sAbkTuATG1iVgaE3GI6QfS9H3NCefPM0RxV0pDT9dFOSV47c17YjzUKxJkofjlD/9zniFv7uMz8kEY9c5k03mQaH03t/HR55iyGUyYk2IaXB1dv703WnGhdOPr888vW3d3Onbls6vjAs87Lo29Ix3a1mWqr5cLW8iv/GlxcWzp0yxHBJraE06Nd0aVVd7q6atQdNaQy2doxQZBpooKtKnvzyxqSiZerhjEBI7gFGT+iJNa21Xu3xwuerdNtNmv/8uaIQ14S9/ZHt+hAtoA5WMt8b5sykz3vnPUGYXI8RcyseD1jI+/hzuy6NtTX46V+6kV19yXvJGv5cGw/V9xr6dcAk8kZeACWvyaDJtb69a3qSSVMrKs6V593WZ6UNOFAZ65JwZZKCJvsyt8Hdjm/3yqf9YPf0Vy9W3zZ+y1X4W7ps/TKJkvBmMKX973mT5rJOb8i7sPbph6ZGNKy4d+O7ARqem/AdtVXkqaZVWqQs55MbGWsjDJpVIDB7odGvVPWplp7JFK2/QNIs7pGWq2iJVtcDYUtTy701/eSL1cscax320rwZlTYa6jA5CKPHSZ6CRlzpnEy7cPDjxri2f8vqLXgcPDG52MULMFQI8dK0U4FUCvBruy6NhTX5SV64kab011wXDdbaV7gQugbF1CZiwJuTJCMa4p6uTLJrTIsxoyL5XnRRadjugJPh4Pnt/9qlvh+hLiF5/9mnjgRMypuL41kurp79iYeDkxjb7d557ViNvnDlpEpV4zdfLy5w1ST28Ot332zT/PcIQ35gT+9fazZZkxTUXpyqqizpa6rs0ugxm/j4+5vKw5fP5bD8/6iUVA0uSrWlaxO0Soaq2UFllwpokhVzQHyb54LVX9eNOBmpNNC3VXYNdRgchhIu2g8ac1Hw396/fnzL51UHMLkaIuSYXD6/W5uJ1cF8eWWsy6l9CGxTDdQ5j3y64BJ6wS8CENSE5QjDGXWoVybTWVJhC1hnWJae/SB9KDKzByMd4hMylgnWdM/mVZ35tIUXblD/9rvxhNEltMh4h0oX//HWCufKhO5fyz24vDPLisb0+mvRy9YMwaW5ia1mmsq5ULW/q7dGFbngzGPoWhCIlMVE/VLa3p7tb06GbniOr0zRVdUiEytoCk3lgg054blnxGfXy+V/9siItZlDWRLfCX5eqtbe7s98L0vQNrnjvT0aOLmhqjJWF4zaWjX6D+1FxNG3GtJdpLmsGNLsYIebGPPz49E0e3jSi9+WYTxFh1qa0J/C+PLJX7kbfqQhNnWf7X35zYmz+pF93MlqXwPLl6G+7y8xfAjGzyBd5mu8BsCaWrQmV2qRT1dbeWK0QFzbmJ9XyokgS2KJrh/PO7844QRsWaxK0+dPxCLkvestcAdc5k8cjZC4eljb79TPfrVluZ0esiUattmxN2BvnZ538Ju/C3mLOkX89+zvRPU4tL7Ip/0FbVb7+bB2Msauzs/GEnTAORz91bE93V7emQ5cHVlanbqxsl5SoagsUpqbneGxeT80cTrt9ZXAxsIq+xYfVLdVdqpbeQa3whxDCwu9/GtriOxUhhFzOmS1TtucFpIeFkjYlv+8XT3rlRetnFyPE3FqAR0bbCvD2kbMmLl5P4n35sV65MXsWlJm4TF7wjTcoucoFIYTejjG3q/gFs75YZf1xy/a88PiuL8YWO8vuZJQugZipfR7aLs7kJVD22dRBWZOHvq+RHa+KSWjA9xti7BFCyOWY+UvAzxmZZJp3mTjMxfR7CL19tGz0rQmZiPvahAnkpaatRdVQJa/MbcxPqk2JEMVdLbvlVxTklRuwM9Pn62GxJkkejs/9/GdL/vOihTK02a+bcydnN8z9cvoksv7weISqRSIOm/3mhD+b29Xx1R+kHl7NP+1WGOjx6rjfld0+R1LENpfwFDUl6taGbq3uQb5GrXZycDDIYc/286sWiaiXvboU9XIyatI3PSdfZWp6zppF89hH9pG//Tx2zZv6xqCtiYosPqxq6ekazAp/CCFcetj2tMN3qrmroz+mLiozs9uyvS8ihGbt3WH1sd6NGf1TYZUkD/fazX7T7pOPrJldjBBzdxEeYe0pwnuLMB2syTBakyF/bWK+ML5k1rgghL5YY1j4/LvI5Haiu4tm9XvJkD2M1PXF2LKQsc/dpi6BL5aTXs/65I65S6DPmjjGWLoEknz/pTuBszak4Og6HHulz0y87XulAXuvRtTLG/1YE5dzuksgxhEh9GNrsjK87xLI932b7M8WrAk1EVcuk/X29qpljSpJpayC35ifVPMgrDIukEzP4ft/l+HjMizWJNVr5evPPv3RS3+xXGbRay+MR8g4LWySh+N7fx+/bcN60ux8Pp/DZr85Yby5/Xgsm5nqtTL9+Hr+2e2T//Rs8jnPyrjAmuRw3brEDSJNWzOVYlWjVm9wdNT3IoZJTbo6uzWqznaZtq1BK9OlqFeZSVG/aPq0+Gt+ir4Y2DWL5g3Omigq0lU1BZpmcZeyefDWpPy47WmP7zRrbIgppjmUUfvhOsyyWHYWfU/fsWbGjH6vh6rYiy6TXnnR78zp/j505vfFeBR1oBh7DMaalH311iC/FG96lj2B1mQ4vjNl9IkIIYRmxpi+WHTXBSnmcsGqa9blwnF8Yab5T4O61jbEWNrhUMVw+585dzIKl0CA6aGIF/eUDdiaXOrb1coY4s4vMHUf3cIg3QOdo5Q7cYp50qyJRq0mv/HR4eEk2ZqyrrS1PLsx7351UmjlnUulYaNjTe4yPn/92adNTuf56KW/0De5kGanJCZy2Oy3/mZ6b3cZnxNrknp4Vdapb98YPy7U07Us8mxVQnB9RmxTYUpbdWFHc32XWqV/QrbTaJQ7MUpqou3RtD9aPUda3rd6jomZw/OmvkElWKMtXaSfFnbQ1qS7c3CTNRCu+GFMiek7DSH0Xow1hROW9WNN9jEHtkObl4x/lLbq4xnvTrUwuxghprcQ24iOCPFRsCaDsybD9J2J+RKhaf/wvfCNi+6K0N++rGwgF13Zvn8gRGqRKsjlwsAv2+ETw20JY99uG7gEYvqGjFzWCrF3vO+L5NUXMT++BKyyJicZ5K42a30KGTgs+1p3XbgcehRrUrb5bd0hJ3uWPVHWBGM8e8oUkiOEmp7TWpYlzU0kM4cfhzUZj9Anr/6132JnN8wdj9CLT/3SYM6O65zJzp/O0R81mfvvf5jbA8mYkuq1Mv2o8xt//F3QnvXCsJMVMRf74mH70pz0PEplplGrPdzd5TIZNlqWuadT09OX1ETbWqNu7EtqYrR6jqIifcrfnqPWHF40fRr1cGdw1kTdJOpUNvdoBztqUhkwpuTpOw0h9H7MAKpsdvlR+c0uCKEvNw9hh7YuXqj7lDf+ydhPNzm7GCHmiTJsazpZhk+V4dPwQMd6azLMX5vNLgjN2udJbYn5EqFpX5RVBuDEL0xYfPKWkcoubja1fdSuMsbWpcbuZGQvgT7DgWb9f/buNK6pM+8f//f/a+97Oq+5O9OZsTN2HcaplrG1Q1ER00VmWivTsVPG1hb3aDWli0pbp1qtgkVB1Epsq2hdUjdQVNCqQW0txY1VIvtyRFbZdwIJ6/V/cJIQkpCwBJKDn/fr+0DCyeEAucjHc20zfmTbOeblqXm5evEH7O1xbAcR0Sx59ybAifkgMl66S9ME5P/kj5wgPaA/DDZW+jTROD9uuI01YYyt8fYeQeTm5GQ+mlhrrEnYx6+NIPJye6o3B8+bNHoE0fSxjxkEjledn9aNNZEGBPxjzKMmn75q+nj97Xie+t39B/87JzN0E3d6V96PoXdiz1WmXq4vSGuuLG5V1uv/TPjRtYwxg10ANdFEWa3Wbex3J0NZmGwyT/z1wd/r1lub+uzTZ2XS/keT4jR1ZV5rY2VHS3/vmuR/Z6+1uW/dOs6zOVPn4dY9QUQSmfYR2d+JSOQ8W97tq/xdboULtqNSZe/x+egNp2f+ajy7mMj321xmz7Unl+3NZftMRBPt3+W+GLeBG57RxMqvmY9NvCM5z+YMWlD0bBGRaN3mfrVl27Qyn4/fMkgnQ9gE5C9of5Yv7GPf5rJv92l+zKPWcppjNI+I3v6Je5v/izdLvidX/iJp/q3fBLZrbpnQk76cJp1rx8COXc/dFTN0GGPhoaH823xB7i1VbYXyzq2a3CSDsSY3d3+aILXODJ21Hi4jiHYvebk3B59bPeOhe+8ZQaQ/nTjaz/P5Jx7XDZHZtHZtT4NqDQLQU7/9P5n3G+kH/bJPSPnV68sUl2pyEhrv5KhqK9pbTUx+MX3XRD+aFKf3FE1GEOnfQdGtWN+PaipKVVfmtTRWdrQ09yGS6P+BKwyx1wqSjiei0dLLxg++JNc/8vI8ERGNn8eZPs+nEr3PyucYPN3UCYdLZUZtdR0/1kuyWH92MZHvwTwmlDqUzw7nsyOIJlZvuQdeIiKa86l+MxH5BukOkM/hW02QdLxe47o8T9T9MP5Ig0d6aMs2a2U+n3j6rF1tmyYgM3tThCQrNAeI5vzMzdFGk4N58r8TEdHodZxeE5C/3OeXv9ZE6WlLTSBqk240s/SKkl3drPlwwmbOvqJJYV4e/zYfKpO1NNY2VRTWF6ZXpl6+EyfnN9DJCAlM2ff5jR3LB74a7PWAOa+Mefip3/6q98fz66AYrNI2dewo3bomn0oWLXrO0fiJ0X6eBsvLPvXbX+374LXkPSszQjblnNqVd/Fw8bXT/I2TpvKCloaa3keTFr1o0mhqKVjjaKLr3OlXh06KqvJ2S2Nle7+jSdEJe62vpeOJ6EnpFeMHp8r1j7yyUERE4xdyBk+fY/DICVa0RkJEc9bwH8rnTJVrTmhS968i2AoO8HIcM0o3u5jI91ghE2KFFbLjhejQsU7L5a4s1LxnahrOGgmRyPdr3QHyOUTjF3IHp5Jek2EHpxKR5KDeeXyfNGwv/FN6ybDZDlb5fDJHl06Guglclo4hIqIxvtxnc/jvWrr9AP/jl3ym+YdowWXtaKo58mOFmhQyxpfTP9VXviIiyZquJsBJtANNNg54XZP9CzW/kznhmiZwaBEREblKY+0qmjDGnB0c+NVN2lSavf1qsuLKkn4sjD556+ze7BPS9EMbknZ6x25ZOMBccupTD/OLmphMGKPv/+UIItl703QP/tvpiRFE/MaES/7zL5Mn3L3k5bUeLvqPjL7/l7slryTtWJ5+0C/reBA/kbjsxsWanISGoixVbUV7q+FIDsNo0tbCjzWxGE1yY+VOjz5kxWjSXJHbUl/erm7qZzS587291u6eQ4PJv3GLuT4/fZrc3GHT5Lb/IVinShUH3P8xyf2Vl0pLS4l8TxczodeZYnb2Lo8m1mtiIt/d7NpiEwNKxk/TvPNpGxfn60jkKL1mcB5f3Z0Bke/unr+QjRuUz4r5PutW8z/VIX25XpU6EhGRoy/no40muzQzfiU+mn+IFl/lFmujyeli+TTtU8w1Ae2Zaa6810uuyWda/LtojuSoPUQTfpmQR+67r7a6SlVb0VhySzN/+ErE7YvW7NNZ9Jzj6Pt/qT/+ozfFL8Km32vz/svP8uNjGGOvTXwm0PN542fNmzT61Kce+o/wHUkJQe/2/sYJv02PXjRp7VAr25Q1LXWl6hpz0SQz+pRBNNGNO+lHNRYlN1fktjQMIJqUnLfX2iedYLGd6JnwLmf2hPK5RDRWes3kV/mnXPOhn6QXpxJoRR7Z4DhmFJHv+RImrPKbO6C/pkREE6Vnhlk0sVYrW++n/wi3fmxXMzn8T+2PT9NAuPVj9RrLeaMnUg+fNWhlNiufFWKfdWuGugnolkQzQeJ3WBNN3r3Gvcv/xZsrP1+iGdw6dj3Xi3Yheu/aQFeDvbTJIJpKDtlNEzARTXTDTUJlslZlXVNVcX1hemXalW5r1cvWJu1YHjOAPp0jy1596N57TMYI83Vu9YwRRA/de48u02ye/fwIoiWenowx54d+r5uGo18Gg2f5kxxZ9mrs5gVJO73TD/plHZdavHFiEE0629va1MrWplp+2+Hmck55x/TefsbRpN+5pCE3rrEoRVXOR
 
 

 创建触发器

CREATE OR REPLACE TRIGGER aiu_itemfileAFTER INSERTON itemfileFOR EACH ROWBEGIN IF (:NEW.qty_hand = 0) THEN  DBMS_OUTPUT.PUT_LINE('警告：已插入记录，但数量为零'); ELSE  DBMS_OUTPUT.PUT_LINE(‘已插入记录'); END IF;END;

查看表的触发器

select * from all_triggers where table_name =upper('tbname')  

 

CREATE OR REPLACE TRIGGER TR_SEC_EMPBEFOR INSERT OR UPDATE OR DELETE ON EMP2BEGIN IF TO_CHAR(SYSDATE,’DY’, ‘nls_date_language=AMERICAN’) IN (‘SAT’, ‘SUN’) THEN RAISE_APPLICATION_ERROR(-20002,’禁止修改数据！’);END IF;END;

工资一般来说都是往上调整，写个触发器禁止降低工资？

create or replace trigger guo_trigger----创建触发器 before update on emp2 ----指明触发器时机 for each row ----行触发器标识 when (new.sal<old.sal ) ----触发条件 begin raise_application_error(-20500,'不能给员工减少工资'); end; 

 

 触发器类型

触发器的类型有：

 

 

DDL 触发器
　　在模式中执行 DDL 语句时执行
数据库级触发器
　　在发生打开、关闭、登录和退出数据库等系统事件时执行
DML 触发器
　　在对表或视图执行DML语句时执行
语句级触发器
　　无论受影响的行数是多少，都只执行一次
行级触发器
 　　对DML语句修改的每个行执行一次
INSTEAD OF 触发器
　　用于用户不能直接使用 DML 语句修改的视图
 

综述：

触发器组成: 
l         触发事件：引起触发器被触发的事件。 例如：DML语句(INSERT, UPDATE, DELETE语句对表或视图执行数据处理操作)、DDL语句（如CREATE、ALTER、DROP语句在数据库中创建、修改、删除模式对象）、数据库系统事件（如系统启动或退出、异常错误）、用户事件（如登录或退出数据库）。
l         触发时间：即该TRIGGER 是在触发事件发生之前（BEFORE）还是之后(AFTER)触发，也就是触发事件和该TRIGGER 的操作顺序。
l         触发操作：即该TRIGGER 被触发之后的目的和意图，正是触发器本身要做的事情。 例如：PL/SQL 块。
l         触发对象：包括表、视图、模式、数据库。只有在这些对象上发生了符合触发条件的触发事件，才会执行触发操作。
l         触发条件：由WHEN子句指定一个逻辑表达式。只有当该表达式的值为TRUE时，遇到触发事件才会自动执行触发器，使其执行触发操作。
l         触发频率：说明触发器内定义的动作被执行的次数。即语句级(STATEMENT)触发器和行级(ROW)触发器。

语句级(STATEMENT)触发器：是指当某触发事件发生时，该触发器只执行一次；行级(ROW)触发器：是指当某触发事件发生时，对受到该操作影响的每一行数据，触发器都单独执行一次。

触发器不接受参数。
l         一个表上最多可有12个触发器，但同一时间、同一事件、同一类型的触发器只能有一个。并各触发器之间不能有矛盾。
l         在一个表上的触发器越多，对在该表上的DML操作的性能影响就越大。
l        触发器最大为32KB。若确实需要，可以先建立过程，然后在触发器中用CALL语句进行调用。
l         在触发器的执行部分只能用DML语句（SELECT、INSERT、UPDATE、DELETE），不能使用DDL语句（CREATE、ALTER、DROP）。
l         触发器中不能包含事务控制语句(COMMIT，ROLLBACK，SAVEPOINT)。因为触发器是触发语句的一部分，触发语句被提交、回退时，触发器也被提交、回退了。
l         在触发器主体中调用的任何过程、函数，都不能使用事务控制语句。
l         在触发器主体中不能申明任何Long和blob变量。新值new和旧值old也不能向表中的任何long和blob列。

l         不同类型的触发器(如DML触发器、INSTEAD OF触发器、系统触发器)的语法格式和作用有较大区别。

 

行级触发器

CREATE TABLE TEST_TRG     (ID NUMBER, NAME VARCHAR2(20));CREATE SEQUENCE SEQ_TEST;
CREATE OR REPLACE TRIGGER BI_TEST_TRG　　BEFORE INSERT OR UPDATE OF ID　　ON TEST_TRG　　FOR EACH ROWBEGIN--函数UPDATING, DELETING判断触发器是由哪个操作触发的    IF INSERTING THEN SELECT SEQ_TEST.NEXTVAL INTO :NEW.ID FROM DUAL;   ELSE    RAISE_APPLICATION_ERROR(-20020, '不允许更新ID值！');   END IF;END;

 

修改数据前备份员工工资

CREATE OR REPLACE TIGGER TR_SAL_BACK AFFTER UPDATE OF SAL ON EMP2 FOR EACH ROW DECLARE 　　V_TEM INT ;BEGIN 　　SELECT COUNT(*) INTO V_TEMP FROM NEW_BACK WHERE ENAME =:OLD.ENAME;　　IF V_TEMP =0 THEN 　　　　INSERT INTO NEW_BACK VALUES (:OLD.ENAME,:OLD.SAL, :NEW.SAL,SYSDATE);　　ELSE 　　　　UPDATE NEW_BACK VALUES SET OLDSAL=:OLDSAL, NEWSAL=:NEW.SAL,TIME=SYSDATE WHERE NAME=:OLD.ENAME;　　END IF; END;

 

建立一个触发器, 当职工表 emp 表被删除一条记录时，把被删除记录写到职工表删除日志表中去。

CREATE TABLE emp_his AS SELECT * FROM EMP WHERE 1=2; CREATE OR REPLACE TRIGGER tr_del_emp BEFORE DELETE --指定触发时机为删除操作前触发  ON scott.emp  FOR EACH ROW  --说明创建的是行级触发器 BEGIN  --将修改前数据插入到日志记录表 del_emp ,以供监督使用。  INSERT INTO emp_his(deptno , empno, ename , job ,mgr , sal , comm , hiredate )    VALUES( :old.deptno, :old.empno, :old.ename , :old.job,:old.mgr, :old.sal, :old.comm, :old.hiredate );END;DELETE emp WHERE empno=7788;DROP TABLE emp_his;DROP TRIGGER del_emp;

语句级触发器

CREATE OR REPLACE TRIGGER trgdemo  AFTER INSERT OR UPDATE OR DELETE  ON order_masterBEGIN  IF UPDATING THEN   DBMS_OUTPUT.PUT_LINE('已更新 ORDER_MASTER 中的数据');  ELSIF DELETING THEN   DBMS_OUTPUT.PUT_LINE('已删除 ORDER_MASTER 中的数据');  ELSIF INSERTING THEN   DBMS_OUTPUT.PUT_LINE('已在 ORDER_MASTER 中插入数据');  END IF;END;

 

行级与语句区别

行触发器和语句触发器的区别表现在：行触发器要求当一个DML语句操走影响数据库中的多行数据时，对于其中的每个数据行，只要它们符合触发约束条件，均激活一次触发器；而语句触发器将整个语句操作作为触发事件，当它符合约束条件时，激活一次触发器。当省略FOR EACH ROW 选项时，BEFORE 和AFTER 触发器为语句触发器，而INSTEAD OF 触发器则只能为行触发器。

INSTEAD OF 触发器

CREATE OR REPLACE TRIGGER upd_ord_view　　INSTEAD OF UPDATE ON ord_view　　FOR EACH ROWBEGIN　　UPDATE order_master　　SET vencode=:NEW.vencode 　　　　WHERE orderno = :NEW.orderno;　　DBMS_OUTPUT.PUT_LINE('已激活触发器');END;

 

 模式触发器

CREATE TABLE dropped_obj 
(　　obj_name VARCHAR2(30),　　obj_type VARCHAR2(20),　　drop_date DATE
);
CREATE OR REPLACE TRIGGER log_drop_obj　　AFTER DROP ON SCHEMABEGIN　　INSERT INTO dropped_obj　　VALUES( ORA_DICT_OBJ_NAME, ORA_DICT_OBJ_TYPE, SYSDATE);END;

 

 启用、禁用和删除触发器

启用和禁用触发器

ALTER TRIGGER aiu_itemfile DISABLE;

ALTER TRIGGER aiu_itemfile ENABLE;

 

删除触发器

DROP TRIGGER aiu_itemfile;

 查看有关触发器的信息

USER_TRIGGERS 数据字典视图包含有关触发器的信息

SELECT TRIGGER_NAME FROM USER_TRIGGERSWHERE TABLE_NAME='EMP';

SELECT TRIGGER_TYPE, TRIGGERING_EVENT, WHEN_CLAUSEFROM USER_TRIGGERSWHERE TRIGGER_NAME = 'BIU_EMP_DEPTNO';

 

 

 

原标题：Oracle学习笔记十三 触发器

关键词：oracle