{"id":608,"date":"2017-09-19T22:20:47","date_gmt":"2017-09-19T22:20:47","guid":{"rendered":"http:\/\/www.manualsqlserver.com\/?p=608"},"modified":"2020-05-18T22:49:48","modified_gmt":"2020-05-18T22:49:48","slug":"triggers-instead-of-en-sql-server","status":"publish","type":"post","link":"https:\/\/manualsqlserver.com\/?p=608","title":{"rendered":"Triggers Instead Of en SQL Server"},"content":{"rendered":"\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_TriggersInsteadOf_D-1.png\" alt=\"\" class=\"wp-image-1000\" width=\"260\" height=\"220\"\/><\/figure>\n\n\n\n<h1 class=\"wp-block-heading\"><strong>Triggers de Tipo Instead of<\/strong><\/h1>\n\n\n\n<p>Los triggers instead of son un tipo de Triggers&nbsp;que reemplazan las instrucciones que hace que se dispare, use estos tipos de Triggers cuando es necesario comprobar algunas condiciones al momento de realizar transacciones con los registros de tablas o vistas.<\/p>\n\n\n\n<p>Por ejemplo: si se crea un Trigger de tipo instead of para la tabla Clientes al insertar un registro,\u00a0al ejecutar un Insert es cuando este tipo de Trigger\u00a0se va a ejecutar en lugar de la instrucci\u00f3n Insert en la tabla o vista.<br><script>&lt;br \/>      (adsbygoogle = window.adsbygoogle || []).push({});&lt;br \/> <\/script><\/p>\n\n\n\n<!--more-->\n\n\n\n<h3 class=\"wp-block-heading\"><strong>Notas importantes<\/strong><\/h3>\n\n\n\n<p>1. Estos tipos de triggers cancelan&nbsp;la instrucci\u00f3n que hace que se dispare, reemplazando<br>esta por las instrucciones del Trigger.<br>2. Generalmente se utilizan estos triggers en vistas<br>3. Las acciones que realice el trigger no deben cancelar la transacci\u00f3n que la dispara sino ejecutar las instrucciones cambiadas que son el cuerpo del trigger.<\/p>\n\n\n\n<script async src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script>\n<ins class=\"adsbygoogle\"\n     style=\"display:block; text-align:center;\"\n     data-ad-layout=\"in-article\"\n     data-ad-format=\"fluid\"\n     data-ad-client=\"ca-pub-2636008503986218\"\n     data-ad-slot=\"1902193434\"><\/ins>\n<script>\n     (adsbygoogle = window.adsbygoogle || []).push({});\n<\/script>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>Ejemplo<\/strong><\/h3>\n\n\n\n<p><strong>Usando la base de datos Northwind<\/strong><\/p>\n\n\n\n<p>use Northwind<br>go<\/p>\n\n\n\n<p>Para utilizar un Trigger de tipo instead of crearemos&nbsp;una vista llamada <strong>SociosEstrategicos<\/strong> con los Clientes<br>(Customers) y los Proveedores (Suppliers)<\/p>\n\n\n\n<p><strong>Primero: los Clientes<\/strong><\/p>\n\n\n\n<p>select C.CustomerID &#8216;C\u00f3d.Cliente&#8217;, C.CompanyName As &#8216;Cliente&#8217;,<br>C.Country As &#8216;Pa\u00eds&#8217;<br>from Customers As C<br>go<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2017\/09\/ManualSQL_TriggersInsteadOf_01.png\" alt=\"\" class=\"wp-image-1002\" width=\"774\" height=\"376\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2017\/09\/ManualSQL_TriggersInsteadOf_01.png 680w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2017\/09\/ManualSQL_TriggersInsteadOf_01-300x146.png 300w\" sizes=\"auto, (max-width: 774px) 100vw, 774px\" \/><\/figure><\/div>\n\n\n\n<p>Segundo: los proveedores<\/p>\n\n\n\n<p>select S.SupplierID As &#8216;C\u00f3d. Proveedor&#8217;, S.CompanyName As &#8216;Proveedor&#8217;,<br>S.Country As &#8216;Pa\u00eds&#8217;<br>from Suppliers As S<br>go<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2017\/09\/ManualSQL_TriggersInsteadOf_02.png\" alt=\"\" class=\"wp-image-1003\" width=\"759\" height=\"352\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2017\/09\/ManualSQL_TriggersInsteadOf_02.png 664w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2017\/09\/ManualSQL_TriggersInsteadOf_02-300x139.png 300w\" sizes=\"auto, (max-width: 759px) 100vw, 759px\" \/><\/figure><\/div>\n\n\n\n<p><strong>Al juntar los dos listados en la vista SociosEstrategicos, la instrucci\u00f3n Select para la vista es como sigue:<\/strong><\/p>\n\n\n\n<p>select C.CustomerID &#8216;C\u00f3d.Cliente&#8217;, C.CompanyName As &#8216;Cliente&#8217;,<br>C.Country As &#8216;Pa\u00eds&#8217;<br>from Customers As C<br>union all<br>select Cast(S.SupplierID As nchar(5)) , S.CompanyName ,<br>S.Country<br>from Suppliers As S<br>go<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2017\/09\/ManualSQL_TriggersInsteadOf_03.png\" alt=\"\" class=\"wp-image-1004\" width=\"808\" height=\"332\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2017\/09\/ManualSQL_TriggersInsteadOf_03.png 797w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2017\/09\/ManualSQL_TriggersInsteadOf_03-300x123.png 300w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2017\/09\/ManualSQL_TriggersInsteadOf_03-768x316.png 768w\" sizes=\"auto, (max-width: 808px) 100vw, 808px\" \/><\/figure><\/div>\n\n\n\n<p>Incluiremos una columna para diferenciar el tipo de socio, Cliente o Proveedor. &nbsp;Adem\u00e1s de cambiar los nombres de campos (<a href=\"http:\/\/www.manualsqlserver.com\/?p=289\">Ver opciones de consultas Union<\/a>)<\/p>\n\n\n\n<p>select C.CustomerID &#8216;CodigoSocio&#8217;, C.CompanyName As &#8216;Socio&#8217;,<br>C.Country As &#8216;Pa\u00eds&#8217;, &#8216;Cliente&#8217; As Tipo<br>from Customers As C<br>union all<br>select Cast(S.SupplierID As nchar(5)) , S.CompanyName ,<br>S.Country , &#8216;Proveedor&#8217; As Tipo<br>from Suppliers As S<br>go<\/p>\n\n\n\n<p>En la imagen se muestra parte de los Clientes y Proveedores.<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2017\/09\/ManualSQL_TriggersInsteadOf_04.png\" alt=\"\" class=\"wp-image-1005\" width=\"844\" height=\"323\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2017\/09\/ManualSQL_TriggersInsteadOf_04.png 804w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2017\/09\/ManualSQL_TriggersInsteadOf_04-300x115.png 300w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2017\/09\/ManualSQL_TriggersInsteadOf_04-768x294.png 768w\" sizes=\"auto, (max-width: 844px) 100vw, 844px\" \/><\/figure><\/div>\n\n\n\n<p><strong>Creamos la vista (<a href=\"http:\/\/www.manualsqlserver.com\/?p=310\">Ver Vistas<\/a>)<\/strong><\/p>\n\n\n\n<p>Create view VistaSociosEstrategicos<br>As<br>select C.CustomerID &#8216;CodigoSocio&#8217;, C.CompanyName As &#8216;Socio&#8217;,<br>C.Country As &#8216;Pa\u00eds&#8217;, &#8216;Cliente&#8217; As Tipo<br>from Customers As C<br>union all<br>select Cast(S.SupplierID As nchar(5)) , S.CompanyName ,<br>S.Country , &#8216;Proveedor&#8217; As Tipo<br>from Suppliers As S<br>go<\/p>\n\n\n\n<p><strong>Si se desea visualizar los socios estrat\u00e9gicos del negocio solamente listamos la vista<\/strong><\/p>\n\n\n\n<p>select * from VistaSociosEstrategicos<br>go<\/p>\n\n\n\n<script async src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script>\n<ins class=\"adsbygoogle\"\n     style=\"display:block; text-align:center;\"\n     data-ad-layout=\"in-article\"\n     data-ad-format=\"fluid\"\n     data-ad-client=\"ca-pub-2636008503986218\"\n     data-ad-slot=\"1902193434\"><\/ins>\n<script>\n     (adsbygoogle = window.adsbygoogle || []).push({});\n<\/script>\n\n\n\n\n<p><strong>El Trigger se crear\u00e1 para la vista, ser\u00e1 de tipo Instead Of (<a href=\"http:\/\/www.manualsqlserver.com\/?p=385\">Ver Triggers<\/a>)<\/strong><\/p>\n\n\n\n<p>create trigger trVistaSocioEstrategicoInsertar<br>on VistaSociosEstrategicos<br>instead of insert<br>as<br>insert into Customers (CustomerID, CompanyName, Country)<br>select CodidoSocio,Socio,Pa\u00eds<br>from inserted where Tipo=&#8217;Cliente&#8217;<\/p>\n\n\n\n<p>insert into Suppliers (CompanyName, Country)<br>select Socio,Pa\u00eds<br>from inserted where Tipo=&#8217;Proveedor&#8217;<br>go<\/p>\n\n\n\n<p><strong>Al ejecutar una inserci\u00f3n en la vista<\/strong><br><script>&lt;br \/>      (adsbygoogle = window.adsbygoogle || []).push({});&lt;br \/> <\/script><\/p>\n\n\n\n<p><strong>Insertar un Cliente<\/strong><\/p>\n\n\n\n<p>insert into VistaSociosEstrategicos<br>values (&#8216;YT348&#8242;,&#8217;Sociedad SQL&#8217;,&#8217;France&#8217;,&#8217;Cliente&#8217;)<br>go<\/p>\n\n\n\n<p><strong>Listamos los clientes (tabla Customers)<\/strong><\/p>\n\n\n\n<p>select * from Customers where CompanyName Like &#8216;Soci%&#8217;<br>go<\/p>\n\n\n\n<p><strong>Insertar un Proveedor<\/strong><\/p>\n\n\n\n<p>insert into VistaSociosEstrategicos<br>values (&#8216;BT599&#8242;,&#8217;Limbo Almacenes&#8217;,&#8217;Spain&#8217;,&#8217;Proveedor&#8217;)<br>go<\/p>\n\n\n\n<p><strong>Listamos los Proveedores (tabla Suppliers)<\/strong><\/p>\n\n\n\n<p>select * from Suppliers where CompanyName Like &#8216;Limbo%&#8217;<br>go<\/p>\n\n\n\n<p><strong>La vista tiene insertados los dos registros<\/strong><\/p>\n\n\n\n<p>select * from VistaSociosEstrategicos<br>where Socio like &#8216;Limbo%&#8217; &nbsp;or Socio like &#8216;Soci%&#8217;<br>go<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2017\/09\/ManualSQL_TriggersInsteadOf_05.png\" alt=\"\" class=\"wp-image-1006\" width=\"827\" height=\"142\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2017\/09\/ManualSQL_TriggersInsteadOf_05.png 652w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2017\/09\/ManualSQL_TriggersInsteadOf_05-300x52.png 300w\" sizes=\"auto, (max-width: 827px) 100vw, 827px\" \/><\/figure><\/div>\n\n\n\n<p>Note que al <a href=\"http:\/\/www.manualsqlserver.com\/?p=294\">insertar registros<\/a> en la vista debemos especificar todos los campos, en el Trigger para los proveedores no se especifica el c\u00f3digo porque es un campo entero y es Identidad. (<a href=\"http:\/\/www.manualsqlserver.com\/?p=329\">Ver Identity<\/a>)<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Triggers de Tipo Instead of Los triggers instead of son un tipo de Triggers&nbsp;que reemplazan las instrucciones que hace que se dispare, use estos tipos de Triggers cuando es necesario comprobar algunas condiciones al momento de realizar transacciones con los registros de tablas o vistas. Por ejemplo: si se crea un Trigger de tipo instead &hellip; <\/p>\n<p><a class=\"more-link btn\" href=\"https:\/\/manualsqlserver.com\/?p=608\">Seguir leyendo<\/a><\/p>\n","protected":false},"author":1,"featured_media":1000,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4,8,9],"tags":[37,39,51,59,64],"class_list":["post-608","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-consultasdedatos","category-programacion","category-registros-vistas","tag-insert","tag-instead-of","tag-select","tag-triggers","tag-vistas","nodate","item-wrap"],"_links":{"self":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/608","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=608"}],"version-history":[{"count":1,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/608\/revisions"}],"predecessor-version":[{"id":1007,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/608\/revisions\/1007"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/media\/1000"}],"wp:attachment":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=608"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=608"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=608"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}