-- cluscode 
CREATE TRIGGER tr_cluscode_add ON dbo.cluscode
FOR INSERT, UPDATE 
	AS
		-- clean up old data
		DELETE FROM occcodes
			FROM inserted i
			WHERE 
					occcodes.codetype = '01' and 
					occcodes.stfips = i.stfips and
					occcodes.code = i.cluscode
		
		-- insert new data
		INSERT INTO occcodes 
			SELECT i.STFIPS, '01' , i.CLUSCODE, i.clustitle
			FROM inserted i
go

CREATE TRIGGER tr_cluscode_del ON dbo.cluscode
FOR delete 
	AS
		DELETE FROM occcodes 
			FROM deleted d
			WHERE 
					occcodes.codetype = '01' and 
					occcodes.stfips = d.stfips and
					occcodes.code = d.cluscode
go

-- oescode	
CREATE TRIGGER tr_oescode_add ON dbo.oescode
FOR INSERT, UPDATE 
	AS
		-- clean up old data
		DELETE FROM occcodes
			FROM inserted i
			WHERE 
					occcodes.codetype = '02' and 
					occcodes.code = i.oescode
		
		-- insert new data
		INSERT INTO occcodes 
			SELECT distinct s.stfips, '02', i.oescode, i.oestitle
			FROM inserted i, statelst s
go

CREATE TRIGGER tr_oescode_del ON dbo.oescode
FOR delete 
	AS
		DELETE FROM occcodes 
			FROM deleted d
			WHERE 
					occcodes.codetype = '02' and 
					occcodes.code = d.oescode
go


-- cipcode		
CREATE TRIGGER tr_cipcode_add ON dbo.cipcode
FOR INSERT, UPDATE 
	AS
		-- clean up old data
		DELETE FROM occcodes
			FROM inserted i
			WHERE 
					occcodes.codetype = '03' and 
					occcodes.code = i.cipcode
		
		-- insert new data
		INSERT INTO occcodes 
			SELECT distinct s.stfips, '03', i.cipcode, i.ciptitle
			FROM inserted i, statelst s
go

CREATE TRIGGER tr_cipcode_del ON dbo.cipcode
FOR delete 
	AS
		DELETE FROM occcodes 
			FROM deleted d
			WHERE 
					occcodes.codetype = '03' and 
					occcodes.code = d.cipcode
go

-- dotcode
CREATE TRIGGER tr_dotcode_add ON dbo.dotcode
FOR INSERT, UPDATE 
	AS
		-- clean up old data
		DELETE FROM occcodes
			FROM inserted i
			WHERE 
					occcodes.codetype = '04' and 
					occcodes.code = i.dotcode
		
		-- insert new data
		INSERT INTO occcodes 
			SELECT distinct s.stfips, '04', i.dotcode, i.dottitle
			FROM inserted i, statelst s
go

CREATE TRIGGER tr_dotcode_del ON dbo.dotcode
FOR delete 
	AS
		DELETE FROM occcodes 
			FROM deleted d
			WHERE 
					occcodes.codetype = '04' and 
					occcodes.code = d.dotcode
go
	
	
--siccode
CREATE TRIGGER tr_siccode_add ON dbo.siccode
FOR INSERT, UPDATE 
	AS
		-- clean up old data
		DELETE FROM indcodes
			FROM inserted i
			WHERE 
					indcodes.codetype = '05' and 
					indcodes.code = i.siccode
		
		-- insert new data
		INSERT INTO indcodes 
			SELECT distinct s.stfips, '05', i.siccode, i.sictitle
			FROM inserted i, statelst s
go

CREATE TRIGGER tr_siccode_del ON dbo.siccode
FOR delete 
	AS
		DELETE FROM indcodes 
			FROM deleted d
			WHERE 
					indcodes.codetype = '05' and 
					indcodes.code = d.siccode
go
	
-- censcode	
CREATE TRIGGER tr_censcode_add ON dbo.censcode
FOR INSERT, UPDATE 
	AS
		-- clean up old data
		DELETE FROM occcodes
			FROM inserted i
			WHERE 
					occcodes.codetype = '06' and 
					occcodes.code = i.censcode
		
		-- insert new data
		INSERT INTO occcodes 
			SELECT distinct s.stfips, '06', i.censcode, i.censtitle
			FROM inserted i, statelst s
go

CREATE TRIGGER tr_censcode_del ON dbo.censcode
FOR delete 
	AS
		DELETE FROM occcodes 
			FROM deleted d
			WHERE 
					occcodes.codetype = '06' and 
					occcodes.code = d.censcode
go
	
-- cidscode
CREATE TRIGGER tr_cidscode_add ON dbo.cidscode
FOR INSERT, UPDATE 
	AS
		-- clean up old data
		DELETE FROM occcodes
			FROM inserted i
			WHERE 
					occcodes.codetype = '07' and 
					occcodes.code = i.cidscode
		
		-- insert new data
		INSERT INTO occcodes 
			SELECT distinct s.stfips, '07', i.cidscode, i.cidstitle
			FROM inserted i, statelst s
go

CREATE TRIGGER tr_cidscode_del ON dbo.cidscode
FOR delete 
	AS
		DELETE FROM occcodes 
			FROM deleted d
			WHERE 
					occcodes.codetype = '07' and 
					occcodes.code = d.cidscode
go
	
-- soccode
CREATE TRIGGER tr_soccode_add ON dbo.soccode
FOR INSERT, UPDATE 
	AS
		-- clean up old data
		DELETE FROM occcodes
			FROM inserted i
			WHERE 
					occcodes.codetype = '08' and 
					occcodes.code = i.soccode
		
		-- insert new data
		INSERT INTO occcodes 
			SELECT distinct s.stfips, '08', i.soccode, i.soctitle
			FROM inserted i, statelst s
go

CREATE TRIGGER tr_soccode_del ON dbo.soccode
FOR delete 
	AS
		DELETE FROM occcodes 
			FROM deleted d
			WHERE 
					occcodes.codetype = '08' and 
					occcodes.code = d.soccode
go

-- stprogcd
CREATE TRIGGER tr_stprogcd_add ON dbo.stprogcd
FOR INSERT, UPDATE 
	AS
		-- clean up old data
		DELETE FROM occcodes
			FROM inserted i
			WHERE 
					occcodes.codetype = '09' and 
					occcodes.stfips = i.stfips and
					occcodes.code = i.code
		
		-- insert new data
		INSERT INTO occcodes 
			SELECT i.STFIPS, '09' , i.code, i.title
			FROM inserted i
go

CREATE TRIGGER tr_stprogcd_del ON dbo.stprogcd
FOR delete 
	AS
		DELETE FROM occcodes 
			FROM deleted d
			WHERE 
					occcodes.codetype = '09' and 
					occcodes.stfips = d.stfips and
					occcodes.code = d.code
go

	
--naiccode
CREATE TRIGGER tr_naiccode_add ON dbo.naiccode
FOR INSERT, UPDATE 
	AS
		-- clean up old data
		DELETE FROM indcodes
			FROM inserted i
			WHERE 
					indcodes.codetype = '10' and 
					indcodes.code = i.naicscode
		
		-- insert new data
		INSERT INTO indcodes 
			SELECT distinct s.stfips, '10', i.naicscode, i.naicstitle
			FROM inserted i, statelst s
go

CREATE TRIGGER tr_naiccode_del ON dbo.naiccode
FOR delete 
	AS
		DELETE FROM indcodes 
			FROM deleted d
			WHERE 
					indcodes.codetype = '10' and 
					indcodes.code = d.naicscode
go


--moscode	
CREATE TRIGGER tr_moscode_add ON dbo.moscode
FOR INSERT, UPDATE 
	AS
		-- clean up old data
		DELETE FROM occcodes
			FROM inserted i
			WHERE 
					occcodes.codetype = '11' and 
					occcodes.code = i.moscode
		
		-- insert new data
		INSERT INTO occcodes 
			SELECT distinct s.stfips, '11', i.moscode, i.mostitle
			FROM inserted i, statelst s
go

CREATE TRIGGER tr_moscode_del ON dbo.moscode
FOR delete 
	AS
		DELETE FROM occcodes 
			FROM deleted d
			WHERE 
					occcodes.codetype = '11' and 
					occcodes.code = d.moscode
go	
	
	
-- onetcode
CREATE TRIGGER tr_onetcode_add ON dbo.onetcode
FOR INSERT, UPDATE 
	AS
		-- clean up old data
		DELETE FROM occcodes
			FROM inserted i
			WHERE 
					occcodes.codetype = '12' and 
					occcodes.code = i.onetcode
		
		-- insert new data
		INSERT INTO occcodes 
			SELECT distinct s.stfips, '12', i.onetcode, i.onettitle
			FROM inserted i, statelst s
go

CREATE TRIGGER tr_onetcode_del ON dbo.onetcode
FOR delete 
	AS
		DELETE FROM occcodes 
			FROM deleted d
			WHERE 
					occcodes.codetype = '12' and 
					occcodes.code = d.onetcode
go	

-- occdir
CREATE TRIGGER tr_occdir_add ON dbo.occdir
FOR INSERT, UPDATE 
	AS
		-- clean up old data
		DELETE FROM occcodes
			FROM inserted i
			WHERE 
					occcodes.codetype = '13' and 
					occcodes.stfips = i.stfips and
					occcodes.code = i.matoccode
		
		-- insert new data
		INSERT INTO occcodes 
			SELECT i.STFIPS, '13' , i.matoccode, i.matocctitl
			FROM inserted i
go

CREATE TRIGGER tr_occdir_del ON dbo.occdir
FOR delete 
	AS
		DELETE FROM occcodes 
			FROM deleted d
			WHERE 
					occcodes.codetype = '13' and 
					occcodes.stfips = d.stfips and
					occcodes.code = d.matoccode
go