SQL: Using a trigger to ensure data integrity.

This trigger will on insert and update look at to fields (e.employerid and e.companyid).
The e.employerid is related to e.companyid though h.companyid.
If there is not match between e.companyid and h.companyid (meaning one of the fields are invalid)
then an error will be thrown back to .NET telling the values of the mismatched ids.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[Trig_employeValidateCompanyId] 
   ON  [dbo].[Employe] 
   --INSTEAD OF INSERT,UPDATE 
   AFTER INSERT,UPDATE
/* info   
	Table:
	deleted contains rows BEFORE change (for update, delete)
	inserted contains rows AFTER change (for insert, update)
	The tables are a mirror of what is put in the transaction log
*/
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON; -- print (5 row(s) affected) removed

	declare @sUserId as varchar(10),
			@iCid1 as int,
			@iCid2 as int
	declare @myrowcount as int
/*
--print 1 -- debug
	-- Any work to do?
	set @myrowcount = @@ROWCOUNT -- does not work - returns 0 even though 1 is updated
	-- exit if no updates
	if @myrowcount = 0 RETURN
	*/

--print 2 -- debug
	-- exit if not relevant columns updated
	if not (update(companyid) or update(employerid)) RETURN

--print 3 -- debug
	-- tæl antal inserted/updated, hvor der er mismatch på companyid
	SELECT @myrowcount = count(*)
		FROM inserted e
		left outer join HRP h ON e.employerid = h.employerid
		where e.employerid is not null
		and e.companyid <> h.companyid

	if @myrowcount > 0
	begin
		select top 1 @sUserId = e.userid, @iCid1 = e.companyid, @iCid2 = h.companyid 
			FROM inserted e left outer join HRP h ON e.employerid = h.employerid
			where e.employerid is not null and e.companyid <> h.companyid
		RAISERROR ('Mismatch in table EMPLOYE for user [%s]. The EMPLOYE.COMPANYID [%i] does not match HRP.COMPANYID [%i]' 
					, 16, 1, @sUserId, @iCid1, @iCid2)
		-- There are no rollback. The update/insert is done before this trigger. The error is thrown back to bizzlayer, so it can log the error as a warning
		RETURN
	end

--print 4 -- debug

END

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: