-- John Aromando -- SQL Automation Procedures --

use ogcPubLev3

--backup Sales table
select *
into SalesBKP
from sales

--create composit view of all sales data suitable for archiving
Create View AllSalesData
as SELECT 
	c.custnum, custname, address, city, state, zipcode,
	r.repid,lname, 
	s.ordnum, sldate, qty, 
	t.partnum, bktitle, pubdate, slprice,
	qty*slprice as OrderTotal 
FROM Customers as c 
		JOIN Sales as s ON s.custnum = c.custnum 
		JOIN Titles as t ON t.partnum = s.partnum 
		JOIN Slspers as r ON r.repid = c.repid

--verify view	
select * from AllSalesData

--create archive table
select *
into AllSalesTransactions
from AllSalesData

--verify
select * from AllSalesTransactions

--empty Archive table in preparation for process and set Primary Key
truncate table AllSalesTransactions
go

alter table AllSalesTransactions 
alter column ordnum nvarchar(5) NOT NULL
go

alter table AllSalesTransactions 
add constraint pkeyAST primary key (ordnum)
go

--verify
select * from AllSalesTransactions

--test if records also still in Sales table
select * from Sales

--empty sales table
truncate table Sales

--verify
select * from AllSalesData

--repopulate table from ogcPublev1
insert Sales 
select * from ogcpublev2.[dbo].[Sales]
--select * from SalesBKP

--verify
select * from AllSalesData

--begin building automation

create procedure sp1_MoveSalesToArchive
as
insert AllSalesTransactions
select * from AllSalesData

create procedure sp2_ClearSalesTable
with execute as self --grants permission to user
as                   --which would otherwise fail
truncate table Sales 

create procedure sp3_VerifyProcess
as
select *, '' as 'Table Name Sales'
from Sales
select *, 'SalesArchive' as 'Table Name'
from AllSalesTransactions

--create procedure to launch prior 3 procedures
create procedure sp0_ArchiveSales
as
exec sp1_MoveSalesToArchive
exec sp2_ClearSalesTable
exec sp3_VerifyProcess

--execute the sp0_ArchiveSales procedure
exec sp0_ArchiveSales;

--manually verify
select * from AllSalesData
select * from AllSalesTransactions

--begin building trigger

--reset Sales table and Archive table
insert sales 
select * from SalesBKP

truncate table AllSalesTransactions

CREATE TRIGGER trig_ArchiveProcess
ON [Sales]
after insert 
as
exec sp0_ArchiveSales

insert sales
(ordnum,sldate,qty,custnum,partnum,repid)
values
('00201','2007-08-01',300,20503,40125,'S02'),
('00202','2007-08-01',300,9989,40232,'S02');