/* code to accompany Shridhar Balasubrmanian's article */


CREATE TABLE CHANGE_CODES
(Table_Name varchar (30) NOT NULL,	-- Primary Key1
Column_Name varchar (30) NOT NULL,	-- Primary Key2
Change_Bit int NOT NULL,
Active char (1) NOT NULL)
CREATE TABLE LOAN_CHANGES
 (Loan_Number varchar (7) NOT NULL,	-- Primary Key
Total_Change_Bit int NOT NULL)	
--
CREATE TABLE LOAN_CHANGES
 (Loan_Number varchar (7) NOT NULL,	-- Primary Key
Total_Change_Bit int NOT NULL)	

--
CREATE TABLE LOAN
(Loan_Number varchar(7) NOT NULL,	-- Primary Key
Mailing_Address varchar (40) NULL,
Mailing_City_State varchar (30) NULL,
Mailing_Zip varchar (10) NULL,
Business1_Phone_Number varchar (14) NULL,
Business1_Ext varchar (5) NULL,
Home1_Phone_Number varchar (14) NULL)

--
-- Declare and Initialize local variables that stores 
-- the total & original bit values associated with a loan. 
declare @Total_Change_Bit int, @Original_Change_Bit 
   int	 
select 	@Total_Change_Bit = 0, @Original_Change_Bit = 0	
--Get the current total bit value
select	@Original_Change_Bit = IsNull 
   (lc.Total_Change_Bit,0)
   from	LOAN_CHANGES lc, inserted i	  	
	 where lc.Loan_Number = i.Loan_Number
-- if we are checking, for example, Mailing_Address, 
-- our script would look like this:
IF UPDATE(Mailing_Address)
select @Total_Change_Bit  = IsNull (@Total_Change_Bit
   + cc.Change_Bit ,0) from   CHANGE_CODES cc
where   cc.Table_Name  = "LOAN" 
and cc.Column_Name = "Mailing_Address"
and cc.Active = "Y"
and @Original_Change_Bit & cc.Change_Bit = 0
--
IF @Total_Change_Bit != 0
begin
if exists (select  * from  LOAN_CHANGES lc ,inserted i
where lc.Loan_number = i.Loan_Number)
update LOAN_CHANGES
set lc.Total_Change_Bit =  
lc.Total_Change_Bit + @Total_Change_Bit
from LOAN_CHANGES lc ,inserted i
where lc.Loan_number = i.Loan_Number
else
insert LOAN_CHANGES(Loan_Number,Total_Change_Bit)
select i.Loan_Number,@Total_Change_Bit from inserted i
end
--
create view vw_LOAN_report
as
select   
l.Loan_Number, "Mailing_Address"
= SubString (l.Mailing_Address,v.Mailing_Address_Bit,
40),"Mailing_City_State" =SubString 
(l.Mailing_City_State,v.Mailing_City_State_Bit,30),
"Mailing_Zip" = SubString (l.Mailing_Zip,
v.Mailing_Zip_Bit,10),"Business1_Phone_Number" 
= SubString (l.Business1_Phone_Number,
v.Business1_Phone_Number_Bit,14),
"Business1_Ext" = SubString (l.Business1_Ext,
v.Business1_Ext_Bit,5),"Home1_Phone_Number" 
= SubString (l.Home1_Phone_Number,
v.Home1_Phone_Number_Bit,14)
from loan l, vw_LOAN_changed_columns v 
where l.Loan_Number = v.Loan_Number

