SQL triggers updated

/*1.    Crear una función que devuelva el numero de empleados*/
go
CREATE FUNCTION fn_q_emp(/*parametros*/)
returns int as
begin
    /*Declarar variables (comienza con @)*/
    declare @qemp int
    /*Query*/
    select @qemp=Count(e.EmployeeID)
    from Employees e
    /*Retornar variable*/
    return @qemp
end
go

select dbo.fn_q_emp()


/*2.    Crear una función que devuelva el número de subordinados un jefe (Empleado)*/
go
CREATE FUNCTION fn_q_subj2(@jefeID int)
returns int as
begin
    declare @numemp int
    select @numemp=count(e.EmployeeID)
    from employees e join employees j on e.ReportsTo=j.EmployeeID
    where j.EmployeeID=@jefeID
    return @numemp
end
go

select dbo.fn_q_subj2(5) as Subordinados

/*3.    Crear una función que liste el número de órdenes por empleado, si solo se conoce parte del nombre del empleado.*/

go

go

CREATE FUNCTION fn_OrdxEmpName(@firstletters nvarchar(10))
returns TABLE as
return
(
    select e.EmployeeID, e.FirstName, COUNT(o.OrderID) as QOrdenes
    from Employees e join Orders o on e.EmployeeID=o.EmployeeID
    where e.FirstName LIKE @firstletters
    group by e.EmployeeID, e.FirstName
)

select *
from fn_OrdxEmpName('%A%A%')

/* 4.   Crear una función que liste para un País (Parámetro de entrada), el Nombre de la compañía (CUSTOMERS),
Ciudad (CUSTOMERS), País (CUSTOMERS), Nombre de Producto, Cantidad (QUANTITY), Precio Unitario y Descuento de Producto.*/


CREATE FUNCTION fn_ordenes(@pais nvarchar(15))
returns table as
return
    (select c.CompanyName, c.City, c.Country, p.ProductName, od.Quantity, p.UnitPrice, od.Discount
    from Customers c join Orders o on o.CustomerID=c.CustomerID join [Order Details] od on od.OrderID=o.OrderID join Products p on p.ProductID=od.ProductID
    where c.Country like @pais)

select *
from dbo.fn_ordenes('c%')



/*5.    Ejecute la función Cos(0), luego la función Getdate( ) repetidas veces*/

select cos(0) --DETERMINISTICA
select getdate() --NO DETERMINISTICA

/*6.    Crear  un procedimiento almacenado que liste el nombre de la compañía, nombre del contacto, ciudad y número de teléfono de los clientes*/

--PROCEDIMIENTO ALMACENADO=PROCEDIMIENTO=PROCEDURE=STORE PROCEDURE
go
create procedure sp_detalle_clientes /*parametro sin paréntesis*/
as
    select c.CompanyName, c.ContactName, c.City, c.Phone
    from customers c
go

exec sp_detalle_clientes

/* 7.	Crear un procedimiento almacenado que actualice el precio unitario de los productos
en un determinado porcentaje para una categoría. (Parámetros % = real, categoria  = entero)*/

--1) Revisar el estado inicial

select *
from Products o
where o.CategoryID=2

/*
3	Aniseed Syrup	1	2	12 - 550 ml bottles	10.00	13	70	25	0
4	Chef Anton's Cajun Seasoning	2	2	48 - 6 oz jars	22.00	53	0	0	0
*/

--2) Realizar la transaccion

alter procedure sp_upd_price @per real, @cat int
as
begin transaction --SOLO PARA LAS TRANSACCIONES
	update Products
	set UnitPrice=UnitPrice+UnitPrice*(@per/100)
	where CategoryID=@cat

	if @@ERROR!=0
		goto on_error
	else
		goto fin

	on_error:
		rollback transaction
	fin:
		commit


exec sp_upd_price 10,1

--1	Chai	1	1	10 boxes x 20 bags	18.00	39	0	10	0
--2	Chang	1	1	24 - 12 oz bottles	19.00	17	40	25	0
--3)Comprobar la ejecucion

select *
from Products
where CategoryID=1


/* 9.	Crear un trigger que notifique en una tabla eventos(fecha, motivo), si las unidades en stock de la(s) tupla(s) de tabla PRODUCTS es menor a 5.
En la entidad eventos, fecha de tipo “DATETIME” y motivo de tipo Varchar(255) */

create table Eventos
(Fecha datetime, Motivo varchar(255))

select *
from Eventos

create trigger tr_productos
--1)Donde y cuando se dispara el trigger
--tabla y transaccion
on products
for update
as
begin
	--2)Que debe hacer el trigger
if update(UnitsInStock)
 begin
	insert into Eventos
	select getdate() 'Fecha','Limite de stock del producto '+ rtrim(ltrim(str(p.ProductID))) 'Limite de stock'
	from inserted p /*inserted o deleted*/
	where p.UnitsInStock<=5
 end
end

--1 Revisar estado inicial
select *
from Products

select *
from Eventos
-- <<<<<<<<<<<<<<<<<<<<<<<<<<<<<1	Chai	1	1	10 boxes x 20 bags	19.406	39	0	10	0
--2 Ejecutar la transaccion
update Products
set UnitsInStock=3
where ProductID=1

update Products
set ProductName='Chai'
where ProductID=1
--3 Revisar la ejecución
select *
from Eventos

delete
from Eventos
where
/*while (@@ERROR=0)
insert into Eventos
select getdate() 'Fecha','Limite de stock del producto '+ rtrim(ltrim(str(p.ProductID))) 'Limite de stock'
from Products p
where p.UnitsInStock<=5*/



/*10.	Sean las dos tablas siguientes:
/*Tabla Departamento
CDepartamento int PK,
NDepartamento varchar(255),
NLocalidad varchar(255),
QEmpleados int */
/* EMPLEADO
CEmpleado int PK,
NEmpleado varchar(255),
CDepartamento int FK */
a)	Crear un trigger que sume +1 el QEmpleados de un departamento cada vez que se inserte un empleado
b)	Crear un trigger que reste -1 el QEmpleados de un departamento cada vez que se eliimne un empleado
*/

go
create table Departamento
(
CDepartamento int NOT NULL,
NDepartamento varchar(255),
NLocalidad varchar (255),
QEmpleados int,
PRIMARY KEY(CDepartamento)
)

create table Empleado
(
CEmpleado int NOT NULL,
NEmpleado varchar(255),
CDepartamento int,
PRIMARY KEY (CEmpleado),
FOREIGN KEY (CDepartamento)REFERENCES Departamento (CDepartamento)
)

create trigger sum_Employees
on Empleado
for insert
as
begin
 update Departamento
 set QEmpleados=QEmpleados+1
 from inserted
 where inserted.CDepartamento=Departamento.CDepartamento
end
go

create trigger del_Employees
on Empleado
for delete
as
begin
 update Departamento
 set QEmpleados=QEmpleados-1
 from deleted
 where deleted.CDepartamento=Departamento.CDepartamento
end
go

select *
from Empleado

insert into Departamento values (3,'Chiclayo', 'Pimentel', 0), (2,'Lima','Miraflores',2)

insert into Empleado values (1,'Ricardo', 1)
select *
from Empleado
select *
from Departamento

delete from Empleado where Empleado.NEmpleado='Ricardo'
/*

create trigger tr_productos
--1)Donde y cuando se dispara el trigger
--tabla y transaccion
on products
for update
as
begin
	--2)Que debe hacer el trigger
if update(UnitsInStock)
 begin
	insert into Eventos
	select getdate() 'Fecha','Limite de stock del producto '+ rtrim(ltrim(str(p.ProductID))) 'Limite de stock'
	from inserted p /*inserted o deleted*/
	where p.UnitsInStock<=5
 end
end*/