CODEWITHSUNDEEP
sql-server
Diana Pereira
Diana Pereira

Reputation: 1

How to change the value STATUS from 1 to 0

I have a table called WRISTBANDS that is connected to a table called PARTICIPANTS.

I want to change the attribute STATUS in the WRISTBANDS table to INACTIVE (= 0) each time a new line is inserted into the WRISTBAND table and is associated with an existing participant in the WRISTBAND table. 

CREATE TRIGGER tg3_after_insert
ON WRISTBANDS
AFTER INSERT 
AS
BEGIN 
    UPDATE WRISTBANDS
    SET STATUS = 0
    WHERE IDPARTICIPANT IN (SELECT IDPARTICIPANT FROM WRISTBANDS)
END 
GO

I've tried this way but what happens is that all the records in the table WRISTBANDS change their attribute STATUS to = 0. And I want the trigger to compare the IDPARTICIPANT from the inserted record to all the records in the WRISTBANDS table and if exists the same IDPARTICIPANT it should change the record that exists to STATUS = 0.

Hope that I have explained myself. I am new to SQL development, just learning.

Thank you.

Upvotes: 0

Views: 1468

Answers (1)

Zohar Peled
Zohar Peled

Reputation: 82524

From what I understand you want to set the status to 0 for existing records, when they have the same IDPARTICIPANT value as the inserted record(s) (please correct me if I'm wrong).

For the purpose of this answer, I'm assuming your WRISTBANDS table have a primary key, and it's a single column called ID (It would work just the same for a composite primary key, this is an assumption made for simplicity).

So you need to use an update with an inner join between the WRISTBANDS table and the inserted table (generated for you by SQL Server), like this (Please read the comments in the code for clarifications):

CREATE TRIGGER tg3_after_insert
ON WRISTBANDS
AFTER INSERT 
AS
BEGIN 

UPDATE T
SET [Status] = 0 -- status is a reserved word in SQL Server, so use square brackets
FROM WRISTBANDS AS T
JOIN INSERTED AS I -- join is equivalent to inner join
    ON T.IDPARTICIPANT = I.IDPARTICIPANT -- only records with the same IDPARTICIPANT 
WHERE T.Id != I.Id -- only records that was not inserted in he statement that fired this trigger

END

Upvotes: 2

Related Questions