September 11, 2012 at 1:04 pm
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.
September 11, 2012 at 1:11 pm
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
September 11, 2012 at 1:23 pm
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
September 11, 2012 at 3:05 pm
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.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply