Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Trigger help

Status
Not open for further replies.

sparkbyte

Technical User
Sep 20, 2002
879
US
I have a table with a list of sites.

PK | Code | Discription | Active | rowguid | modifieddate

1|abc|site1|true
2|def|site2|False
3|ghi|site3|False
4|jkl|site4|False
5|lmn|site5|False
6|opq|site6|False

What I want to have happen is when one of the sites is modified to true. The current site that is set to true becomes false.

So let's say I change site 4 to be active (True) all other sites should be flagged as false as a result.





Thanks

John Fuhrman
 
Code:
UPDATE / INSERT trigger:
IF @@ROWCOUNT = 0
  -- no changes
  RETURN

declare @Pk int, @RowCount int

select @PK = PK from Inserted where Active = true
set @RowCount = @@RowCount
IF @RowCount > 1
   -- This is very bad case - we need to rollback
  begin
   print 'The recent change made more than 1 site active!' 
   ROLLBACK
   return

   end
IF @RowCount = 0
   no sites were made active - all is well
   return

update myTable set Active = false where Active = True 
and PK <> @PK
Be careful - this may lead to recursive triggers!



PluralSight Learning Library
 
If I understand your solution correctly.

If I set a site to active = true and there is already one set as active = true the trigger is going to rollback the transaction leaving the first one still active.

What I was thinking ....

PK | Code | Discription | Active | rowguid | modifieddate

1|abc|site1|true
2|def|site2|False
3|ghi|site3|False
4|jkl|site4|False
5|lmn|site5|False
6|opq|site6|False

RowGUID and ModifiedDate are automatically assigned when a new record is inserted.
ModifiedDate is also updated on any row update via a trigger.

So if site4 is changed to Active = true, this will cause two sites to be true when only one should be. Since site4 was just modified it's ModifiedDate should be the most recent and any site with an older ModifiedDate should be updated to Active = False.

This may be where I start fighting with recursive triggers.

I will probably have to add this check to the other update trigger.

Thanks

John Fuhrman
 
No, you misunderstood my solution.

If you try to set more than 1 site to true in update/insert, we rollback.

If you do it only for one site, we grab the PK of this record and set previously active site to false.

If we didn't change one of the sites to active, we do nothing.

PluralSight Learning Library
 
OK, I see the logic flow now. THANKS I will test it in a little while.




Thanks

John Fuhrman
 
Well I seem to have something wrong.

Code:
USE [MailroomTracking]
GO
/****** Object:  Trigger [dbo].[uiFRC]    Script Date: 12/02/2010 08:14:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[uiFRC_ACTIVE] 
   ON  [dbo].[tblFRC] 
   AFTER INSERT,UPDATE
AS 
BEGIN

	SET NOCOUNT ON;

	IF @@ROWCOUNT = 0
		-- No Changes
	RETURN

	declare @PK int, @RowCount int

	select @PK = FRC_PK from Inserted where FRC_Active = 1
	set @RowCount = @@RowCount
	IF @RowCount > 1
	   -- This is very bad case - we need to rollback

		BEGIN
		   Print 'The recent change made more than 1 site active!' 
		   ROLLBACK
		   Return
		END

	IF @RowCount = 0
	   Print 'No sites were made active - all is well'
	   Return

		Update dbo.tblFRC 
			Set FRC_Active = 0,
			ModifiedDate = GetDate()
			Where FRC_Active = 1 and FRC_PK <> @PK
END


Results.

FRC_PK FRCcode FRC_Description FRC_Active RowGUID ModifiedDate
----------- ------- -------------------------------------------------- ---------- ------------------------------------ -----------------------
1 NRC National Records Center 1 A02864DC-0CFC-485D-91A0-50C9BED0009C 2010-12-01 17:58:16.403
2 NBC National Benefits Center 0 E68D76AC-0CB8-4431-9756-FCEBB6D0D342 2010-12-02 08:27:36.863
3 ABC C 1 A4029BDC-D38E-42EC-8684-F1F426C03092 2010-12-02 09:05:55.620

(3 row(s) affected)


Thanks

John Fuhrman
 
Try removing SET NOCOUNT ON and re-try. I think SET NOCOUNT ON resets ROWCOUNT.

Or move it right after return.

Also you may add more print statements just to see what's going on.

PluralSight Learning Library
 
Not sure...

Code:
USE [MailroomTracking]
GO
/****** Object:  Trigger [dbo].[uiFRC]    Script Date: 12/02/2010 08:14:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[uiFRC_ACTIVE] 
   ON  [dbo].[tblFRC] 
   AFTER INSERT,UPDATE
AS 
BEGIN

--	SET NOCOUNT ON;

	IF @@ROWCOUNT = 0
		Print 'No Changes'
	RETURN

	declare @PK int, @RowCount int

	select @PK = FRC_PK from Inserted where FRC_Active = 1
	set @RowCount = @@RowCount
	IF @RowCount > 1
	   -- This is very bad case - we need to rollback

		BEGIN
		   Print 'The recent change made more than 1 site active!' 
		   ROLLBACK
		   Return
		END

	IF @RowCount = 0
	   Print 'No sites were made active - all is well'
	   Return

		Update dbo.tblFRC 
			Set FRC_Active = 0,
			ModifiedDate = GetDate()
			Where FRC_Active = 1 and FRC_PK <> @PK
END

Code:
Update dbo.tblFRC
	Set FRCcode = 'asd',
		FRC_Active = 1
	where FRC_PK = 3

Select * from dbo.tblFRC

Result:
(1 row(s) affected)
FRC_PK      FRCcode FRC_Description             FRC_Active RowGUID                              ModifiedDate
----------- ------- --------------------------- ---------- ------------------------------------ -----------------------
1           NRC     National Records Center     1          A02864DC-0CFC-485D-91A0-50C9BED0009C 2010-12-01 17:58:16.403
2           NBC     National Benefits Center    0          E68D76AC-0CB8-4431-9756-FCEBB6D0D342 2010-12-02 08:27:36.863
3           asd     C                           1          A4029BDC-D38E-42EC-8684-F1F426C03092 2010-12-02 09:05:55.620

(3 row(s) affected)

Thanks

John Fuhrman
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top