{"id":1452,"date":"2020-05-21T17:34:19","date_gmt":"2020-05-21T17:34:19","guid":{"rendered":"http:\/\/manualsqlserver.com\/?p=1452"},"modified":"2020-05-21T17:34:21","modified_gmt":"2020-05-21T17:34:21","slug":"triggers-historial-de-cambios-en-una-tabla","status":"publish","type":"post","link":"https:\/\/manualsqlserver.com\/?p=1452","title":{"rendered":"Triggers Historial de cambios en una tabla"},"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\/ManualSQLServer_Triggers_Historial___D.png\" alt=\"\" class=\"wp-image-1453\" width=\"221\" height=\"217\"\/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">Triggers Insert y Update &#8211; Creando un Historial de cambios<\/h2>\n\n\n\n<p>Los triggers DML son procedimientos guardados en la base de datos que se disparan cuando se insertan registros, cuando se actualizan los datos de un registro o cuando se eliminan registros.<br>Este ejercicio muestra como crear un Historial de cambios usando un Trigger, el trigger se disparar\u00e1 cuando se inserte o actualice un registro.<br>Para mayor informaci\u00f3n <a href=\"https:\/\/manualsqlserver.com\/?p=385\">Ver Triggers<\/a><\/p>\n\n\n\n<!--more-->\n\n\n\n<p><strong>Usando la base de datos Norhtwind<\/strong><br>use Northwind<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<p class=\"has-medium-font-size\"><strong>Ejercicio 1<\/strong><\/p>\n\n\n\n<p>Historial de cambios en la tabla Shippers.<\/p>\n\n\n\n<p><strong>La tabla Shippers (Compa\u00f1\u00edas de env\u00edo) tiene los campos: ShipperID, Companyname y Phono. Primero se crear\u00e1 una tabla HistorialShippers con los campos C\u00f3digo, Nombre, Tel\u00e9fono y Fecha.<\/strong><br>Create table HistorialShippers<br>(<br>HistorialShippersCodigo nchar(10),<br>HistorialShippersNombre nvarchar(40),<br>HistorialShippersFono nvarchar(24),<br>HistorialShippersFecha Date<br>)<br>go<br><strong>Crear el trigger para la tabla Shippers que se dispare cuando se inserta o modifica un registro<\/strong><br>Create trigger triggerShippersHistorial on Shippers<br>for insert, update<br>As<br>Begin<br>Insert into HistorialShippers<br>select inserted.ShipperID, inserted.CompanyName,<br>inserted.Phone, GetDate()<br>from inserted<br>End<br>go<br><\/p>\n\n\n\n<p>Antes de insertar registros, visualizar los registros en las tablas.<br>En Shippers<br>select * from Shippers<br>go<\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_Triggers_Historial__01.png\" alt=\"\" class=\"wp-image-1454\" width=\"654\" height=\"207\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_Triggers_Historial__01.png 511w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_Triggers_Historial__01-300x95.png 300w\" sizes=\"auto, (max-width: 654px) 100vw, 654px\" \/><figcaption>Registros de la tabla Shippers.<\/figcaption><\/figure>\n\n\n\n<p>En HistorialShippers<br>select * from HistorialShippers<br>go<br>La instrucci\u00f3n muestra que no hay registros.<\/p>\n\n\n\n<p><strong>Insertar un registro en la tabla Shippers, esto har\u00e1 que se dispare el Trigger  y se inserte un registro en la tabla creada HistorialShippers<br><\/strong>insert into Shippers (CompanyName, Phone)<br>values (&#8216;Trainer SQL&#8217;,&#8217;87852541&#8242;)<br>go<br><strong>Prueba con la actualizaci\u00f3n de los datos de un registro.<br>El registro insertado l\u00edneas arriba se gener\u00f3 su c\u00f3digo 4, se cambiar\u00e1 su nombre y tel\u00e9fono.<br><\/strong>update Shippers set CompanyName = &#8216;Capacitador SQL&#8217;,<br>Phone = &#8216;963258741&#8217; where ShipperID = 4<br>go<br><strong>Probando con insertar otro registro<\/strong><br>insert into Shippers (CompanyName, Phone)<br>values (&#8216;Tracks Moves&#8217;,&#8217;952369985&#8242;)<br>go<br>Puede observar que la tabla HistorialShippers tiene los datos de los registros que se insertaron o que se actualizaron.<br>select * from HistorialShippers<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\/2020\/05\/ManualSQLServer_Triggers_Historial__02.png\" alt=\"\" class=\"wp-image-1455\" width=\"745\" height=\"114\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_Triggers_Historial__02.png 948w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_Triggers_Historial__02-300x46.png 300w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_Triggers_Historial__02-768x118.png 768w\" sizes=\"auto, (max-width: 745px) 100vw, 745px\" \/><\/figure><\/div>\n\n\n\n<p class=\"has-medium-font-size\"><strong>Ejercicio 2<\/strong><\/p>\n\n\n\n<p><strong>Se va a crear una tabla Ciudades, el historial de cambios en esta tabla va a incluir la operaci\u00f3n realizada, si es inserci\u00f3n y al realizar una actualizaci\u00f3n se va a guardar el original, el cambio y el nombre del usuario que realiz\u00f3 el cambio.<\/strong><br>Create table Ciudades<br>(<br>CiudadesCodigo nchar(4),<br>CiudadesNombre nvarchar(100),<br>constraint CiudadesPK Primary key (CiudadesCodigo)<br>)<br>go<br><strong>Tabla para el historial de cambios en Ciudades, se va a incluir la operaci\u00f3n que puede ser inserci\u00f3n o modificaci\u00f3n, la fecha y el usuario que la realiz\u00f3.<\/strong><br>Create table HistorialCiudades<br>(<br>HistorialCiudadesCodigo nchar(4),<br>HistorialCiudadesNombre nvarchar(100),<br>HistorialCiudadesOperacion nvarchar(15),<br>HistorialFecha DateTime,<br>HistorialEstado nvarchar(10),<br>HistorialUsuario nvarchar(128)<br>)<br>go<br><strong>Trigger para insertar en la tabla Ciudades<\/strong><br>Create trigger triggerCiudadesInsertar on Ciudades for insert<br>As Begin<br>insert into HistorialCiudades<br>select inserted.CiudadesCodigo, inserted.CiudadesNombre,<br>&#8216;inserci\u00f3n&#8217;, Getdate(), &#8216;insertado&#8217;, SYSTEM_USER<br>from inserted<br>End<br>go<br><strong>Trigger para actualizaci\u00f3n en la tabla Ciudades<\/strong><br>Create trigger triggerCiudadesActualizacion on Ciudades for update<br>As Begin<br>insert into HistorialCiudades<br>select deleted.CiudadesCodigo, deleted.CiudadesNombre,<br>&#8216;modificacion&#8217;, Getdate(), &#8216;original&#8217;, SYSTEM_USER<br>from deleted<br>insert into HistorialCiudades<br>select inserted.CiudadesCodigo, inserted.CiudadesNombre,<br>&#8216;modificacion&#8217;, Getdate(), &#8216;cambiado&#8217;,SYSTEM_USER<br>from inserted<br>End<br>go<br><strong>Ver la tabla historial<\/strong><br>select * from HistorialCiudades<br>go<br><strong>El resultado muestra que no existen registros<\/strong><br><\/p>\n\n\n\n<p><strong>Insertar registros en la tabla Ciudades<\/strong><br>insert into ciudades values (&#8216;2541&#8242;,&#8217;Trujillo&#8217;)<br>go<br>Ver los datos<br>select * from Ciudades<br>select * from HistorialCiudades<br>go<\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_Triggers_Historial__03-1024x144.png\" alt=\"\" class=\"wp-image-1456\" width=\"725\" height=\"102\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_Triggers_Historial__03-1024x144.png 1024w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_Triggers_Historial__03-300x42.png 300w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_Triggers_Historial__03-768x108.png 768w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_Triggers_Historial__03.png 1353w\" sizes=\"auto, (max-width: 725px) 100vw, 725px\" \/><figcaption>Registros de las tablas Ciudades y de Historial de ciudades.<\/figcaption><\/figure>\n\n\n\n<p><strong>Insertar m\u00e1s ciudades<\/strong><br>insert into ciudades values (&#8216;8521&#8242;,&#8217;Piura&#8217;)<br>go<br>insert into ciudades values (&#8216;7458&#8242;,&#8217;Huaraz&#8217;)<br>go<br>insert into ciudades values (&#8216;4169&#8242;,&#8217;Lima&#8217;)<br>go<br>Modificar Piura<br>update Ciudades set CiudadesNombre = &#8216;Piura calor&#8217; where CiudadesCodigo = &#8216;8521&#8217;<br>go<br>Ver los datos<br>select * from Ciudades<br>select * from HistorialCiudades<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\/2020\/05\/ManualSQLServer_Triggers_Historial__04-1024x267.png\" alt=\"\" class=\"wp-image-1457\" width=\"735\" height=\"191\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_Triggers_Historial__04-1024x267.png 1024w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_Triggers_Historial__04-300x78.png 300w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_Triggers_Historial__04-768x200.png 768w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_Triggers_Historial__04.png 1352w\" sizes=\"auto, (max-width: 735px) 100vw, 735px\" \/><\/figure><\/div>\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","protected":false},"excerpt":{"rendered":"<p>Triggers Insert y Update &#8211; Creando un Historial de cambios Los triggers DML son procedimientos guardados en la base de datos que se disparan cuando se insertan registros, cuando se actualizan los datos de un registro o cuando se eliminan registros.Este ejercicio muestra como crear un Historial de cambios usando un Trigger, el trigger se &hellip; <\/p>\n<p><a class=\"more-link btn\" href=\"https:\/\/manualsqlserver.com\/?p=1452\">Seguir leyendo<\/a><\/p>\n","protected":false},"author":1,"featured_media":1453,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[143],"tags":[147,145,149,59],"class_list":["post-1452","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-triggersql","tag-alter-trigger","tag-create-trigger","tag-system_user","tag-triggers","nodate","item-wrap"],"_links":{"self":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/1452","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=1452"}],"version-history":[{"count":1,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/1452\/revisions"}],"predecessor-version":[{"id":1458,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/1452\/revisions\/1458"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/media\/1453"}],"wp:attachment":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1452"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1452"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1452"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}