UPDATE & INSERT Conditional trigger

Question

Post reply

UPDATE & INSERT Conditional trigger

PFlorenzano-641896

SSCommitted

Points: 1643
More actions
September 11, 2012 at 1:04 pm

#281433

Hello,
I'm trying to write a trigger that will do two different things (based on a flag bit setting) which is as follows:
If the ‘IsActive’ flag is ‘1’, then we need to update the record to change the flag to 0, and update the InActiveDate to whatever the date the trigger is being ran at.
If the ‘IsActive’ flag is ‘0’, then we need to insert a new record for that user. You should be able to use the same logic for the insert trigger for this part.
Below is what I have for code:
INSERT TRIGGER:
ALTER TRIGGER [dbo].[Mag_PortalUserInsert] ON [dbo].[Mag_PortalUser] AFTER INSERT AS
--Insert the Audit Record
INSERT INTO Mag_PortalUserAudit (
[Userid],
[Isactive],
[ActiveDate],
[InActiveDate])
SELECT inserted.Userid,
inserted.isactive,
GETDATE(),
NULL
FROM INSERTED
INNER JOIN Mag_PortalUser PU ON PU.Userid = inserted.userId
INNER JOIN dbo.Users_Roles UR ON PU.userid=UR.userid and UR.roleid=10174
WHERE inserted.isActive = 0
UPDATE TRIGGER
ALTER TRIGGER [dbo].[Mag_PortalUserUpdate] ON [dbo].[Mag_PortalUser] AFTER UPDATE AS
--Insert the Audit Record
INSERT INTO Mag_PortalUserAudit (
[Userid],
[Isactive],
[ActiveDate],
[InActiveDate])
SELECT inserted.Userid,
0,
NULL,
GETDATE()
FROM INSERTED
INNER JOIN Mag_PortalUser PU ON PU.Userid = inserted.userId
INNER JOIN dbo.Users_Roles UR ON PU.userid=UR.userid and UR.roleid=10174
WHERE inserted.isActive = 1
It doesn't seem to be firing. I'm thinking I need a conditional statement, but not sure how to craft it.
Any help would be much appreciated.
Thank you.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

just checkign the basic here...I see you are using inner joins, and not left outer joins...

you are sure the inserted.userId will always have a value that exists in the Mag_PortalUser table?

further, every user that would be coming through would always be in the Role 10174? It's not coming in as NULL, is it?

maybe they should be left outer joins, in case they don't exist in the table, or are in a different role than expected?

INNER JOIN Mag_PortalUser PU ON PU.Userid = inserted.userId
INNER JOIN dbo.Users_Roles UR ON PU.userid=UR.userid and UR.roleid=10174

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

PFlorenzano-641896 SSCommitted Points: 1643 More actions · Answer 2

Hello Lowell,

We only want the trigger to fire if both the roleid = 10174 and the userid is in the Mag_PortalUser table. We are inserting the results in an audit table, that's why I'm using the SQL Server Inserted trigger table to perform the join.

I'm wondering if I could use just one trigger to handle both the UPDATE and INSERT within the Mag_PortalUser table, and conditional logic like an IF or CASE statement.

Thanks again

toddasd SSCertifiable Points: 6298 More actions · Answer 3

PFlorenzano-641896 (9/11/2012)
I'm wondering if I could use just one trigger to handle both the UPDATE and INSERT within the Mag_PortalUser table, and conditional logic like an IF or CASE statement.

Yes. Change the trigger type to FOR INSERT, UPDATE and then check the virtual tables. If the DELETED table is empty then an insert operation is being performed, else it is a update operation. Like this:

IF NOT EXISTS (SELECT * FROM deleted)

--this is the insert code

ELSE

--this is the update code

______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.