Another SQL Server Trigger question

[Bulldog13]

Good morning all!

Let us suppose I have a table (tblInfo) with 4 fields. PK, SSN, Date_Created, and Status. Let us assume there are currently 4 records in the field, 3 with the same SSN. Of those 3, the status of 2 are 'inactive' and the other is 'active'.

When I insert a new record into tblInfo, with the same SSN as the other 3, I need the status of those 3 to change to 'inactive' (or stay as inactive if already set) leaving the 4th record with a different SSN alone.

I know I need an insert trigger, but not sure on the actual code of it.

 

[GilletteCat]

CREATE TRIGGER MyTrigger
ON tblInfo
FOR INSERT
AS
UPDATE tblInfo SET Status = 'inactive'
WHERE PK IN (SELECT t.PK FROM tblInfo t, inserted i WHERE t.SSN = i.SSN AND t.PK <> i.PK AND t.Status = 'active')

 

[JACKDRUID]

I assume the newly inserted record would be 'active' while older records being 'inactive'

CREATE TRIGGER trgInsert
on tblInfo
FOR INSERT
AS
-- set new record to active
DECLARE @ID int
SELECT @ID=PK FROM Inserted
UPDATE tblInfo
SET Status = 'active'
WHERE PK=@ID
--set old record to inactive
DECLARE @SSN int
SELECT @SSN = SSN FROM Inserted
UPDATE tblInfo
SET Status = 'inactive'
WHERE SSN= @SSN AND ID <> @ID

 

[GilletteCat]

Originally posted by: Bulldog13
When I insert a new record into tblInfo, with the same SSN as the other 3, I need the status of those 3 to change to 'inactive' (or stay as inactive if already set) leaving the 4th record with a different SSN alone.

JACKDRUID, he does not need to set the new record to 'active'. That's taken care of by his insert query. That's why what he needs can be done in one step, without declaring any variables.

 

[Bulldog13]

Originally posted by: GilletteCat
CREATE TRIGGER MyTrigger
ON tblInfo
FOR INSERT
AS
UPDATE tblInfo SET Status = 'inactive'
WHERE PK IN (SELECT t.PK FROM tblInfo t, inserted i WHERE t.SSN = i.SSN AND t.PK <> i.PK AND t.Status = 'active')

Hahaha. Awesome man! I copied / pasted, renamed the table and that thing worked like a charm.

Thanks for taking the time to check if the field = 'active' then executing the trigger. While just setting all previous records to inactive, even if they were already inactive, would have worked, it would have broken my modificationdate trigger.

Thanks !