use northwind

select * from Customers where TerritoryID is not null
select * from Employees
select * from Territories
select * from EmployeeTerritories
select * from Orders where ShippedDate is null
/*
Problem Problem
We develop a system which couples the trading database of the Northwind Co. running on the PRIM server
instances to the HR database running on the THIRD server.
In the Northwind database, the territories for which an agent (i.e. an employee) is responsible are listed
in the EmployeeTerritories table, which acts as a linking table between the Employees and Territories
tables. A territory is assigned to an employee by inserting a new record in the linking table.
The expected minimum functionality, for 70% of the points is that when a new territory is assigned to an
employee in the trading database, a notification should be sent from the HR database ASAP, to those
customers who are located on the territory and have at least one active (not yet shipped) order. The note
should include the name and phone number of the customer as well as the contact name and company
name of the customer. You can simulate the sending of the notification by printing a message like this:
“Dear Maria Anders at Alfreds Futterkiste, please not that as of today a new Northwind agent, Robert
King, phone (71) 555-5598, is a your service”
The expected full functionality is that you process not only new entries in the EmployeeTerritories table
but also changed records, when an existing record is changed. In this case, send a message to the
customers of the new territory as well as the customers of the unassigned territory, the latter something
like “…we regret to inform you that Mr. XY will no longer be at your service etc”.
*/

/*Stored procedure*/
alter procedure send_message_to_customers @territory_id nvarchar(20), @employee_id int
as
set nocount on
declare @customer_name varchar(50),  @company_name varchar(50), @i int, @str varchar(150), @res_no int
select @res_no=count(*)  from Employees where EmployeeID = @employee_id
if @res_no=0 print 'No matching record.'
else if @res_no>1 print 'More than one matching record.'
else begin --if we have a valid employee
select @str = (FirstName + ' ' + LastName + ' phone ' + HomePhone) from Employees where EmployeeID = @employee_id
begin tran
begin try
    declare customer_cursor cursor for
    select distinct(Customers.ContactName), Customers.CompanyName from Customers join Orders on Customers.CustomerID = Orders.CustomerID where TerritoryID like @territory_id and ShippedDate is null
    set @i=1
    open customer_cursor
    fetch next from customer_cursor into @customer_name, @company_name
    while @@fetch_status = 0
    begin
    print 'Dear ' + @customer_name + ' at ' + @company_name +  ', please not that as of today a new Northwind agent, ' + @str +  ', is a your service!'
     set @i=@i+1
     fetch next from customer_cursor into @customer_name, @company_name
    end
    close customer_cursor
    deallocate customer_cursor
    commit tran
end try
    begin catch
    print 'OTHER ERROR: '+ ERROR_MESSAGE() + ' (' + cast(ERROR_NUMBER() as varchar(20)) +
    ')'
    print 'Rolling back transaction'
    rollback tran
    end catch
end


/*Test stored procedure*/
exec send_message_to_customers '01581', 1

/*Triggers*/

/*Insert trigger*/
create trigger new_agent_inserted on EmployeeTerritories for insert as
declare @employee_id int, @territory_id nvarchar(20)
select @employee_id=EmployeeID, @territory_id = TerritoryID from inserted
exec send_message_to_customers @territory_id, @employee_id
go

/*Test insert trigger*/
delete from EmployeeTerritories where TerritoryID = '01581'
insert into EmployeeTerritories (EmployeeID, TerritoryID) values(9, '01581')

/*Update trigger*/

alter trigger agent_updated on EmployeeTerritories for update as
declare @employee_id int, @territory_id nvarchar(20)
select @employee_id=EmployeeID, @territory_id = TerritoryID from inserted
exec send_message_to_customers @territory_id, @employee_id
go

update EmployeeTerritories set TerritoryID = '01581' where EmployeeID = 9 and TerritoryID = '03049'