xXDrAnCoNsXx
  Base de Datos II
 
--USANDO BDBANCOS
use bdestudios
/*1.-Crear un Trigger que no permita eliminar un Alumno si es que el alumno tiene Notas, es
decir, tiene registros asociados en la tabla nota para un curso. Deberá mostrar como mensaje
"El alumno ………, No puede eliminarse por que tiene Notas".*/
create trigger noeliminaralumno
on alumnos after delete
as
declare @dato varchar(100)
declare @can char(12)
declare @alu varchar(60)
select @dato=codalu from deleted
select @can=(select count(codalu) from notas where codalu=@dato)
select @alu=(select nomalu from alumnos where codalu=@dato)
if @can=1
begin
    raiserror('El alumno: %s , No puede eliminarse por que tiene Notas',14,1,@alu)
    rollback tran
end
go

delete from alumnos where codalu='0000001'
go
/*2.-Crear un Trigger que solo permita como máximo tener 20 alumnos por carrera, si al
momento de la inserción de nuevos alumnos, este llegara a ser numero 21 en esa carrera, no
se permitirá grabarlo y deberá mostrara un mensaje indicando: “El alumno ……… no puede
insertarse, por haber llegado al máximo permitido por carrera”.*/
use bdestudios
/*select c.codcar,count(c.codcar)[matriculados]
from alumnos a , carreras c
where a.codcar=c.codcar
group by c.codcar*/

create trigger limite
on alumnos before insert
as
declare @cod varchar(12)
declare @car int
declare @can int
declare @alu varchar(50)
select @cod=codalu from inserted
select @car=codcar from inserted
select @can=(select count(c.codcar)[matriculados]from alumnos a , carreras c where a.codcar=c.codcar and c.codcar=@car)
select @alu=nomalu from inserted
if(@can<21)
    begin
        set nocount on
        commit tran
    end
else
    begin
        raiserror('El alumno: %s , No puede insertarse por haberse agotado las vacantes',14,1,@alu)
        rollback tran
    end
go
Insert Alumnos(codalu,nomalu,codcar)Values('0000047','German, Mejia Castrejon',1)
/*3.-Crear un Trigger que no permita grabar un nuevo registro en la tabla Notas si es que los
valores a ser insertados en las notas son número no comprendidos entre 0 a 20. Si se tratase
de insertar un valor invalido (numero menor que cero o mayor que 20), deberá mostrar un
mensaje indicando: “El numero…. Es invalido para ser guardado como Nota”.*/
create trigger buenanota
on notas after insert
as
declare @n1 int
declare @n2 int
declare @n3 int
select @n1=pp from inserted
select @n2=pt from inserted
select @n3=ex from inserted
if((@n1 >= 0 and @n1 < 21) and (@n2 >= 0 and @n2 < 21) and (@n3 >= 0 and @n3 < 21) )
    begin
        set nocount on
        commit tran
    end
else
    begin
        set nocount on
        raiserror('El numero . Es invalido para ser Guardado como Nota...!!!',14,1)
    end
go
/*4.-Crear un Trigger que no permita insertar un nuevo registro en la tabla notas, si es que se
pretende insertar un código de alumno o un código de curso que no figuren en sus respectivas
tablas. Al sucederse este hecho deberá mostrar un mensaje indicando que “El código del
alumno ……. No Existe”, ó “El código del curso ……. No Existe”, según corresponda.*/
create trigger validar
on notas after insert
as
declare @codalu varchar(12)
declare @codcur varchar(12)
declare @canalu int
declare @cancur int
select @codalu=codalu from inserted
select @codcur=codcur from inserted
select @canalu=(select count(codalu) from alumnos where codalu=@codalu)
select @cancur=(select count(codcur) from cursos where codcur=@codcur)
if(@canalu = 0)
    begin
        set nocount on
        raiserror('El códio del alumno. No Existe...!!!',14,1)
        rollback tran
    end
if(@cancur=0)
    begin
        set nocount on
        raiserror('El código del curso. No Existe...!!!',14,1)
        rollback tran
    end
if(@canalu=1 and @cancur=1)
    begin
        set nocount on
        commit tran
    end
go

select * from alumnos
select * from cursos
select * from notas

                    codalu,codcur,pp,pt,ex
Insert Notas Values('0000046','C0002',11,12,15)
--USANDO BDBANCOS
/*5.-Crear un Trigger que No permita agregar un mismo tipo de cuenta para un mismo cliente
con el mismo tipo de moneda, es decir, si el cliente ya tiene una cuenta de ahorros en soles, no
puede volver a sacar una nueva cuenta de ahorros en soles. Si se produjese este hecho se
deberá visualizar un mensaje “El cliente ………., ya dispone de una cuenta similar con la
misma moneda”*/
use bdbanco
create trigger nocuenta
on cuentas after insert
as
declare @can int
select @can=(select )

select count(moneda) from cuentas c, tipo_cuentas tp
where c.cod_cta=tp.cod_cta and moneda='s'


/*6.-Crear un Trigger que no permita retirar más de lo que un cliente dispone en su cuenta
respectiva, si esto sucediera, se deberá mostrar un mensaje, “La cantidad ….. que desea retirar
es mayor a lo que dispone”*/
create trigger norretiro
on movimientos after insert
as
declare @mon numeric(10,2)
declare @cue varchar(12)
declare @movi char(1)
declare @n int
declare @mon_re numeric(10,2)
select @cue=num_cta from inserted
select @mon_re=monto from inserted
select @movi=tip_mov from inserted
select @n=(select max(num_mov)from movimientos where num_cta=@cue)
select @mon=(select saldo_nue from movimientos where num_mov=@n)
if(@movi='R')
    begin
        if(@mon > @mon_re)
            begin
                set nocount on
                commit tran
            end
        else
            begin
                set nocount on
                rollback tran
            end
    end
go

--'196-16518-00'
INSERT Movimientos values('196-16518-00','21/10/2002','R',1700,1500,200)
INSERT Movimientos values('196-16518-00','21/10/2002','R',200,100,100)
INSERT Movimientos values('196-16518-00','21/10/2002','R',100,150,-50)
select * from clientes
select * from cuentas
select * from movimientos
select * from tipo_cuentas
/*7.-Crear un Trigger que permita Actualizar el Saldo de una cuenta en la tabla Cuentas por
cada movimiento realizado, recuerde, que si el movimiento es de tipo deposito el saldo en la
tabla Cuentas se incrementara, si el movimiento es de tipo retiro el saldo en la tabla Cuentas
disminuirá para el número de cuenta respectivo.*/
create trigger actualizar
on movimientos after insert
as
declare @mon numeric(10,2)
declare @cue varchar(12)
declare @movi char(1)
declare @n int
declare @canti numeric(10,2)
select @cue=num_cta from inserted
select @mon_re=monto from inserted
select @movi=tip_mov from inserted
select @n=(select max(num_mov)from movimientos where num_cta=@cue)
select @mon=(select saldo_nue from movimientos where num_mov=@n)
if(@movi='R')
    begin
        if(@mon > @mon_re)
            begin
                set nocount on
                commit tran
            end
        else
            begin
                set nocount on
                rollback tran
            end
    end
go
-- (num_cta,fecha,tip_mov,saldo_ant,monto,saldo_nue)

/*8.-Crear un Trigger que lleve un control de la cantidad de operaciones que realiza un cliente,
este control será almacenado en una tabla llamada Control_Operaciones la cual almacenara el
código del cliente, número de cuenta, numero de operaciones por sus depósitos y el número de
operaciones por sus retiros.*/

/*CREATE TABLE Control_Operaciones
(
cli_codigo char(5),
num_cta char(12),
num_oper_dep int,
num_oper_ret int
)*/




bcp Northwind.dbo.Customers out e:carpetaclientes.txt -c -T

bcp Northwind.dbo.Customers out e:carpetaclientes.txt -c -T -S A6_11

bcp "Select E.EmployeeID,LastName+space(2)+FirstName as Empleado,Sum(UnitPrice*Quantity) as Venta_Total From Northwind.dbo.Employees E,Northwind.dbo.Orders
O,Northwind.dbo.[Order Details] d Where E.EmployeeID=O.EmployeeID and O.OrderID=d.OrderID Group by E.EmployeeID,LastName,FirstName" QUERYOUT c:VentadeEmpleados.txt -T -c


select * into newtabla from customers
truncate table newtable

bcp Northwind.dbo.newtable in e:carpetaclientes.txt -c -T -S A6_11

bcp Adventureworks.dbo.Adventureworks.Sales.Currency out e:carpetamonedas.dat -c -T -S A6_11

select * into AdventureWorks.Sales.Monedas from AdventureWorks.Sales.Currency where1=2

bcp Adventureworks.Sales.Monedas in e:carpetamonedas.dat -c -T -S A6_11

VISUAL(Proyecto de Integracion)
CONTROL FLOW
(ponemos un dataflowtask)
Connection Managers
  1. clic derecho - conexion Ado,net
  2. new(localhost, AdventureWorks) -ok
  3. ok
Data Flow
  1. ponemos un AdoNetSource
  2. clic derecho(Advance...)
  3. en connection(seleccionamos la conexion)
  4. en componet - sqlcommand(la consulta "select ProductID,Name,ListPrice from production.product") - ok
  1. jalamos un derive columm y lo enlazamos al AdonetSource. - doble clic
  2. <Precio - "$"+(DT_STR,10,1252)(DT_CY)ListPrice> - ok
  1. FlatFileDestination y lo unes al Columnderive
  2. Advance - new - ruteamos al destino y marcamos el check.
  3. en Advance eliminamos Precio. ok
  4. En mapping muestra los campos a exportar. ok
CONTROL FLOW
  1. jalamos un Scrip Task y lo unimos al dataflowtask.
  2. en scrip(editar script y esperamos a que cargue).
  3. en la parte final: MessageBox.Show("Listo").
  4. exit - ok.   listo.
NUEVO PACKAGE
CONTROL FLOW
  1. @Echo off
    sqlcmd -i "scriptHumanResourcesDB.sql"
  2. (USE master;
    GO
    IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'HumanResources')
        DROP DATABASE HumanResources
    GO
    CREATE DATABASE HumanResources;
    GO
    USE HumanResources;
    GO
    CREATE SCHEMA hr;
    GO

    SELECT e.EmployeeID, p.FirstName, p.LastName, e.Title AS JobTitle
    INTO hr.Employees
    FROM AdventureWorks.HumanResources.Employee e
    JOIN AdventureWorks.Person.Contact p
    ON e.ContactID = p.ContactID
    ORDER BY e.EmployeeID
    GO

    SELECT e.EmployeeID, p.EmailAddress
    INTO hr.EmailAddresses
    FROM AdventureWorks.HumanResources.Employee e
    JOIN AdventureWorks.Person.Contact p
    ON e.ContactID = p.ContactID
    ORDER BY e.EmployeeID
    GO)
  1. conexion con "HumanResources"
  2. jalamos un dataflowtask. - doble clik
DATA FLOW
  1. editar(conexionHuman)
  2. table or view  -  hr. Employes  - ok
  3. jalamos un lockup ylo unimos.
  4. edit - conexionHuman - hr.Addres -column unimos el igual y marcamos la columna a unir.
  5. jalamos un sort y lo unimos
  6. iguales - editar - clumnar a ordenar.
  7. jalamos un condicional split y lo unimos -editar
  8. (Representantes - <JobTitle=="Sales Representatives">)
  9. (No representantes - <JobTitle!="Sales Representatives">)      - ok
  10. jalamos 2 files destination y lo unimos 1 para "Representantes y el otro para NoRepresentantes".
  11. editar 1(new - range - ruteamos en advance ponemos "Employeeid,FirsName,LastName" - la otra sin name lo eliminas - ok).
  12. editar 2(new - range - ruteamos en advance ponemos "Employeeid,FirsName,LastName" - la otra sin name lo eliminas - ok) - listo.








































































































--- TRIGGER A NIVEL DE SERVIDOR ---
--TRIGGER DML--
--EJEMPLO DE INSTEAD OF:CREAR UN TRIGGER QUE SE EJCUTE CUANDO
--SE ELIMINE UNA ORDEN, Y A LA VEZ SE ELIMINE SUS PRODUCTOS
--VENDIDOS QUE ESTEN INVOLUCRADOS.

use Northwind
go
select max(orderid)from orders
delete from orders where orderid=11076
go

alter trigger eliminarencascada
on orders instead of delete
as
    declare @orderid int
    select @orderid=(select orderid from deleted)
    if(select count(*)from [order details],deleted where
        [order details].orderid=deleted.orderid)>0
        begin
            delete from [order details]where orderid=@orderid
            delete from orders where orderid=@orderid
            print 'Los productos vendidos de la orden se eliminaron con exito'
        end
    else
        delete from orders where orderid=@orderid
        print 'Se elimino la orden, pero no tuvo productos vendidos'
go

select max(orderid)from orders
delete from orders where orderid=11077
go


select * from [Order Details] where OrderID=11076
select * from Orders where OrderID=11076

---TRIGGER DDL---DROP,ALTER,CREATE
use Northwind
go
--1.-CREAR UN TRIGGER A NIVEL DE BASE DE DATOS, QUE NO PERMITA LA ELIMINACION DE TABLAS EN LA BASE DE DATOS.
create trigger noeliminartablas
on database after drop_table
as
    select eventdata()
    print 'No se Puede eliminar Tablas'
    rollback
go

create table prueba(cod int, nom varchar(20))
go

drop table prueba
go
--2.CREAR UN TRIGGER A NIVEL DE SERVIDOR, QUE NO PERMITA CREAR NUEVO LOGIN EN SQL SERVER.
create login user01 with password=''
go

create trigger nocrearlogin
on all server after create_login
as
    select eventdata()
    raiserror('No se Puede Crear Logins',14,1)
    rollback
go

create login user01 with password=''

--deshabilitar triggers
--disable trigger nocrearlogin on all server
--go

--ELIMINAR UNA COLUMNA
ALTER TABLE CUSTOMERS ADD CODIGOS VARCHAR(100)
ALTER TABLE CUSTOMERS DROP COLUMN CODIGOS
GO

--3.CREAR UN TRIGGER QUE NO PERMITA LA ELIMINACION DE COLUMNA
--DE CUALQUIER TABLA
create trigger noeliminarcolumna
on database after alter_table
as
    declare @datos xml
    declare @comando varchar(200)
    select @datos=eventdata()
    select @comando=@datos.value('(EVENT_INSTANCE/TSQLCommand/CommandText)[1]','varchar(200)')
    if(@comando like '%drop column%')
        begin
            raiserror('No Se Puede Eliminar Columna',14,1)
            rollback
        end
go

--4.-CREAR UN TRIGGER QUE PERMITA GUARDAR EN UN TABLA AUDITORIA
--LOS INTENTOS DE CREACION DE TABLA
create table auditoria(nro int identity,informacion xml)
go

create trigger nocreartablas
on database after create_table
as
    declare @datos xml
    select @datos=eventdata()
    rollback
    insert auditoria values(@datos)
go

create table base(cod int)
select * from auditoria
go
--PRUEBAS CON USUARIO
create user user01
grant control on schema::dbo to user01
grant create table to user01
--PRUEBA CON CAMBIO DE IDENTIDAD
setuser 'user01'--iniciar sesion
select user
create table procesion(cod int)
setuser--cerrar sesion






 
  Hoy habia 1 visitantes  
 
.:: Publicidad ::.