investigación de lo que son los triggers y los procedimientos almacenados PDF

Preview

Summary

en esta investigación se muestran las definiciones de lo que son los triggers y los procedimientos almacenados, así como también la sintaxis de cada uno, ejemplos de como se utilizan, ventajas y desventajas ...

Description

INVESTIGACION TALLER DE BASE DE DATOS PROFESOR

CINTHIA ANAHI MATA BRAVO ALUMNO VICTOR DANIEL VAZQUEZ MARISCAL UNIDAD 4

Contenido Procedimientos Almacenados / Stored Procedures ............................................................ 1 ¿Qué es un Procedimiento Almacenado (SP)? ............................................................... 1 Ventajas y Desventajas de implementar SP. ................................................................... 2 Ventajas .......................................................................................................................... 2 Desventajas.................................................................................................................. 3 Tipos de procedimientos almacenados............................................................................ 3 Definidos

por el usuario .......................................................................................... 3

Temporales .................................................................................................................. 3 Sistema ........................................................................................................................ 4 Extendidos

definidos por el usuario ....................................................................... 4

Recibir y retornar datos en un SP .................................................................................... 4 Devolución de datos mediante conjuntos de resultados .............................................. 5 Devolución de datos mediante un parámetro de salida ................................................ 5 Sentencias para crear, modificar y eliminar un SP .......................................................... 5 Sintaxis de un procedimiento almacenado con y sin parámetros de entrada y salida. .... 6 Conclusión del tema ........................................................................................................ 9 Disparadores / Triggers ....................................................................................................... 9 ¿Qué es un Trigger? ........................................................................................................ 9 Ventajas y Desventajas de implementar Triggers. ..........................................................10 Tipos de Triggers. ...........................................................................................................11 Sentencias para crear, modificar y eliminar un Trigger ...................................................11 Eventos que desencadenan los Trigggers ......................................................................13 Cuando se ejecutan los Triggers. ...................................................................................13 Sintaxis de un Trigger .....................................................................................................13 Conclusión del Tema ......................................................................................................15 BIBLIOGRAFIA...................................................................................................................16

Procedimientos Almacenados / Stored Procedures ¿Qué es un Procedimiento Almacenado (SP)? Un procedimiento almacenado de SQL Server es un grupo de una o más instrucciones Transact-SQL o una referencia a un método de Common Runtime Language (CLR) de Microsoft .NET Framework. Los procedimientos se asemejan a las construcciones de otros lenguajes de programación, porque pueden: 1

Aceptar parámetros de entrada y devolver varios valores en forma de parámetros de salida al programa que realiza la llamada. Contener instrucciones de programación que realicen operaciones en la base de datos. Entre otras, pueden contener llamadas a otros procedimientos. Devolver un valor de estado a un programa que realiza una llamada para indicar si la operación se ha realizado correctamente o se han producido errores, y el motivo de estos. Ventajas y Desventajas de implementar SP. Ventajas Tráfico

de

red

reducido

entre

el

cliente

y

el

servidor

Los comandos de un procedimiento se ejecutan en un único lote de código. Esto puede reducir significativamente el tráfico de red entre el servidor y el cliente porque únicamente se envía a través de la red la llamada que va a ejecutar el procedimiento Mayor

seguridad

Varios usuarios y programas cliente pueden realizar operaciones en los objetos de base de datos subyacentes a través de un procedimiento, aunque los usuarios y los programas no tengan permisos directos sobre esos objetos subyacentes. El procedimiento controla qué procesos y actividades se llevan a cabo y protege los objetos de base de datos subyacentes La cláusula EXECUTE AS puede especificarse en la instrucción CREATE PROCEDURE para habilitar la suplantación de otro usuario o para permitir que los usuarios o las aplicaciones puedan realizar ciertas actividades en la base de datos sin necesidad de contar con permisos directos sobre los objetos y comandos subyacentes. El uso de parámetros de procedimientos ayuda a protegerse contra ataques por inyección de código SQL.

2

Desventajas Aumentan el uso de la memoria: si usamos muchos procedimientos almacenados, el uso de la memoria de cada conexión que utiliza esos procedimientos se incrementará sustancialmente. Restringidos para una lógica de negocios compleja: en realidad, las construcciones de procedimientos almacenados no están diseñadas para desarrollar una lógica de negocios compleja y flexible. Difíciles de depurar. Es difícil depurar procedimientos almacenados. Solo unos pocos sistemas de administración de bases de datos permiten hacerlo. Difíciles de mantener: no es fácil desarrollar y mantener procedimientos almacenados. El desarrollo y mantenimiento de procedimientos almacenados a menudo requiere un conjunto de habilidades especializadas que no todos los desarrolladores de aplicaciones poseen. Tipos de procedimientos almacenados Definidos

por

el

usuario

Un procedimiento definido por el usuario se puede crear en una base de datos definida por el usuario o en todas las bases de datos del sistema excepto en la base de datos Resource . El procedimiento se puede desarrollar en Transact-SQL o como una referencia a un método de Common Runtime Language (CLR) de Microsoft .NET Framework. Temporales Los procedimientos temporales son una forma de procedimientos definidos por el usuario. Los procedimientos temporales son iguales que los procedimientos permanentes salvo porque se almacenan en tempdb. Hay dos tipos de procedimientos temporales: locales y globales. Se diferencian entre sí por los nombres, la visibilidad y la disponibilidad. Los procedimientos temporales locales tienen como primer carácter de sus nombres un solo signo de número (#); solo son visibles en la conexión actual del usuario y se eliminan cuando se cierra la conexión. Los procedimientos temporales globales presentan dos signos de número (##) antes

3

del nombre; son visibles para cualquier usuario después de su creación y se eliminan al final de la última sesión en la que se usa el procedimiento. Sistema Los procedimientos del sistema se incluyen con SQL Server. Están almacenados físicamente en la base de datos interna y oculta Resource y se muestran de forma lógica en el esquema sys de cada base de datos definida por el sistema y por el usuario. Además, la base de datos msdb también contiene procedimientos almacenados del sistema en el esquema dbo que se usan para programar alertas y trabajos. Dado que los procedimientos del sistema empiezan con el prefijo sp_ , le recomendamos que no use este prefijo cuando asigne un nombre a los procedimientos definidos por el usuario Extendidos

definidos

por

el

usuario

Los procedimientos extendidos le permiten crear sus propias rutinas externas en un lenguaje de programación como puede ser C. Estos procedimientos son DLL que una instancia de SQL Server puede cargar y ejecutar dinámicamente. Recibir y retornar datos en un SP Uso de return RETURN [ integer_expression ]

integer_expression

Es el valor entero que se devuelve. Los procedimientos almacenados pueden devolver un valor entero al procedimiento que realiza la llamada o a una aplicación. Cuando se utiliza con un procedimiento almacenado, RETURN no puede devolver un valor NULL. Si un procedimiento intenta devolver un valor NULL (por ejemplo, al utilizar RETURN @status cuando @status es NULL), se genera un mensaje de advertencia y se devuelve un valor 0. El valor de estado devuelto se puede incluir en las siguientes instrucciones TransactSQL del lote o procedimiento que ha ejecutado el procedimiento actual, pero se debe

escribir

de

la

forma

siguiente:

EXECUTE

@return_status

=

.

4

Devolución de datos mediante conjuntos de resultados Si incluye una instrucción SELECT en el cuerpo de un procedimiento almacenado (pero no en SELECT ... INTO o INSERT ... SELECT), las filas especificadas por la instrucción SELECT se enviarán directamente al cliente. Para conjuntos de resultados grandes, la ejecución del procedimiento almacenado no continuará con la siguiente instrucción hasta que el conjunto de resultados se haya enviado completamente al cliente. Para conjuntos de resultados pequeños, los resultados se pondrán en cola para volver al cliente y la ejecución continuará. Si se ejecutan varias instrucciones SELECT de este tipo durante la ejecución del procedimiento almacenado, se enviarán varios conjuntos de resultados al cliente. Este comportamiento también se aplica a los lotes TSQL anidados, los procedimientos almacenados anidados y los lotes TSQL de nivel superior. Devolución de datos mediante un parámetro de salida Si especifica la palabra clave OUTPUT para un parámetro en la definición del procedimiento, el procedimiento puede devolver el valor actual del parámetro al programa que realiza la llamada cuando se cierra el procedimiento. Para guardar el valor del parámetro en una variable que se puede utilizar en el programa que realiza la llamada, el programa que realiza la llamada debe utilizar la palabra clave OUTPUT al ejecutar el procedimiento. Sentencias para crear, modificar y eliminar un SP Para crear un procedimiento almacenado se utiliza la siguiente sintaxis CREATE (OR ALTER) (PROC | PROCEDURE) procedure_name @parameter data_type (OUT | OUTPUT)(READONLY) EXECUTE AS BEGIN ATOMIC WITH

END procedure_name El nombre del procedimiento. Los nombres de los procedimientos deben cumplir las reglas de los identificadores y deben ser exclusivos en el esquema. @ parameter 5

Parámetro declarado en el procedimiento. Especifique un nombre de parámetro usando una arroba (@) como el primer carácter OUT |

OUTPUT

Indica que se trata de un parámetro de salida. Utilice los parámetros OUTPUT para devolver valores al autor de la llamada del procedimiento. READONLY Indica que el parámetro no se puede actualizar ni modificar dentro del cuerpo del procedimiento. Si el tipo de parámetro es un tipo con valores de tabla, se debe especificar Cláusula EXECUTE

AS

Especifica el contexto de seguridad en el que se ejecuta el procedimiento. BEGIN

END

Una o más instrucciones Transact-SQL que comprenden el cuerpo del procedimiento. Puede usar las palabras clave BEGIN y END opcionales para incluir las instrucciones. ATOMIC WITH Indica la ejecución automática de procedimientos almacenados. Los cambios se confirman o todos se revierten iniciando una excepción. El bloqueo ATOMIC WITH se requiere para los procedimientos almacenados compilados de forma nativa Para modificar se utilizan las palabras reservadas ALTER PROCEDURE seguido del nombre del procedimiento que queremos modificar y posteriormente hacemos los cambios necesarios ALTER PROCEDURE procedure_name @parameter data_type –- aquí se agregarían, modificarían a eliminarían los parámetros (OUT | OUTPUT)(READONLY) –se seleccionaría la nueva opción a usar si es necesario EXECUTE AS BEGIN -- se cambiaria el cuerpo del procedimiento si es necesario END Para borrar un procedimiento solo se escribe la palabra DROP, después PROCEDURE para indicar que va a ser un procedimiento y por último el nombre del procedimiento que queremos borrar DROP PROCEDURE ; GO Sintaxis de un procedimiento almacenado con y sin parámetros de entrada y salida. Con parámetros de entrada Para este ejemplo creamos la BD, una tabla e insertamos datos en la tabla 6

create database procedimientos use procedimientos create table tabla (id int identity, nombre varchar(20), apellido varchar (10)) insert into tabla values ('daniel','mariscal'),('victor','vazquez'),('pedro','lopez'),('devian','devian') select*from tabla

Después creamos el procedimiento almacenado -- se utilizan las palabras reservadas CREATE PROCEDURE seguido del nombre que le asignaremos a nuestro procedimiento create proc parametros @nombre varchar(10)=pedro -- este es un parametro de entrada AS begin inicio del cuerpo del procedimiento select*from tabla where nombre=@nombre-- indica lo que realizara el procedimiento END-- indica que aqui termina nuestro procedimiento Para ejecutar nuestro procedimiento utilizamos la palabra EXEC seguido del nombre del procedimiento que queremos ejecutar y este deberá mostrar los usuarios con nombre = pedro

Sin parámetros de entrada y salida 7

Para este ejemplo creamos dos tablas una llamada PERSONAS y la otra EDAD y después les insertamos datos create table personas (id int identity primary key, nombre varchar (15))

create table edad (ide int identity primary key, edad int) insert into edad values(19),(25),(26),(15),(19),(20) insert into personas values ('persona1'),('persona2'),('persona3'),('persona4'),('persona5'),('persona6')

-- se utilizan las palabras reservadas CREATE PROCEDURE seguido del nombre que le asignaremos a nuestro procedimiento CREATE PROCEDURE personasE AS BEGIN inicio del cuerpo del procedimiento SELECT personas.nombre, edad.edad -- indica lo que realizara el procedimiento FROM personas INNER JOIN edad ON personas.id = edad.ide -- indica de donde tomara los valores necesarios para realizar la consulta en este ejemplo realizara el procedimiento un inner join entre las tablas personas y la tabla edad ORDER BY edad.edad – indica que el resultado se mostrara ordenado por edad RESULTADO

8

Conclusión del tema Como pudimos ver los procedimientos se almacenan en la propia Base de Datos y constituyen un objeto más dentro de esta. Tienden a mejorar el rendimiento de los sistemas producto a que reducen en intercambio entre cliente y servidor. Los procedimientos almacenados son reutilizables, de manera que los usuarios mediante la aplicación cliente no necesitan relanzar los comandos individuales, sino que pueden llamar el procedimiento para ejecutarlo en el servidor tantas veces como sea necesario. Por ejemplo, si deseamos obtener un reporte con datos obtenidos de varias tablas, una buena forma de hacerlo sería con un procedimiento almacenado al ejecutar los procesos mediante procedimientos almacenados estamos aprovechando toda esa capacidad de los sistemas disponibles También son muy útiles cuando múltiples aplicaciones cliente se escriben en distintos lenguajes o funcionan en distintas plataformas, pero necesitan realizar la misma operación en la base de datos. Los procedimientos almacenados también ayudan a la seguridad de la base de datos ya que estos proporcionan un entorno seguro y consistente, y los procedimientos pueden asegurar que cada operación se realice de forma correcta ya que las aplicaciones y los usuarios no obtendrían ningún acceso directo a las tablas de la base de datos

Disparadores / Triggers ¿Qué es un Trigger? Un trigger o disparador es un objeto que se asocia con tablas y se almacena en la base de datos. Su nombre se deriva por el comportamiento que presentan en su funcionamiento, ya que se ejecutan cuando sucede algún evento sobre las tablas a las que se encuentra asociado. Los eventos que hacen que se ejecute un trigger son las operaciones de inserción (INSERT), borrado (DELETE) o actualización (UPDATE), ya que modifican los datos de una tabla. La utilidad principal de un trigger es mejorar la gestión de la base de datos, ya que no requieren que un usuario los ejecute. Por lo tanto, son empleados para implementar las REGLAS DE NEGOCIO de una base de datos. Una Regla de Negocio es cualquier restricción, requerimiento, necesidad o actividad especial que debe ser verificada al momento de intentar agregar, borrar o actualizar la información de una base de datos. Los triggers pueden prevenir errores en los datos, modificar valores de una vista, sincronizar tablas, entre otros.

9

Ventajas y Desventajas de implementar Triggers. Ventajas: Provee una alternativa para la verificación de la integridad en los datos. Puede detectar errores en la lógica de negocio en la base de datos. Se puede utilizar como tareas programadas (Task Schedule). Útiles para auditar los cambios en las bases de datos. Ofrece chequeos de seguridad basada en valores. Restricciones dinámicas de integridad de datos y de integridad referencial. Asegura que las operaciones relacionadas se realizan juntas de forma implícita. Respuesta inmediata ante un evento auditado. Ofrece mayor control sobre la BD. Desventajas Hay que definir con anticipación la tarea que realizara trigger. Peligro de pérdida en realizaciones. Nunca se llama directamente. Solo se pueden aplicar a una tabla específica, es decir, un trigger no sirve para dos o más tablas El trigger se crea en la base de datos que, de trabajo, pero desde un trigger puedes hacer referencia a otras bases de datos. Un Trigger devuelve resultados al programa que lo desencadena de la misma forma que un Stored Procedure aunque no es lo más idóneo, para impedir que una instrucción de asignación devuelva un resultado se puede utilizar la sentencia SET NOCOUNT al principio del Trigger. Pueden aumentar la sobrecarga del servidor de base de datos.

10

Se ejecutan y son invisibles desde las aplicaciones cliente, por lo tanto, es difícil averiguar qué sucede en la capa de base de datos. Tipos de Triggers. Existen los siguientes tipos de Triggers •

•

•

Los Triggers DML se ejecutan cuando se realizan operaciones de manipulación de datos (DML). Los eventos DML son instrucciones INSERT, UPDATE o DELETE realizados en una tabla o vista. Los Triggers DDL se ejecutan al realizar eventos de lenguaje de definición de datos (DDL). Estos eventos corresponden a instrucciones CREATE, ALTER y DROP. Los Triggers Logon, que se disparan al ejecutarse un inicio de sesión en SQL Server.

Sentencias para crear, modificar y eliminar un Trigger • Crear Trigger create trigger NOMBRE TRIGGER MOMENTO-- before, after o instead of EVENTO-- insert, update o delete of CAMPOS-- solo para update on NOMBRETABLA NIVEL--puede ser a nivel de sentencia (statement) o de fila (for each row) CONDICION-- when opcional begin CUERPO DEL DISPARADOR--sentencias end NOMBREDISPARADOR “CREATE TRIGGER” para indicar que crearemos un disparador “NOMBRE TRIGGER” será el nombre con el que identificaremos a nuestro disparador "MOMENTO" indica cuando se disparará el trigger en relación al evento, puede ser BEFORE (antes), AFTER (después) o INSTEAD OF (en lugar de). "before" significa que el disparador se activará antes que se ejecute la operación (insert, update o 11

delete) sobre la tabla, que causó el disparo del mismo. "after" significa que el trigger se activará después que se ejecute la operación que causó el disparo. "instead of" sólo puede definirse sobre vistas, anula la sentencia dispara...