mardi 28 juillet 2015

How to use nested If statements in SQL trigger

I'm trying to learn SQL triggers to automatically handle events in my database but I'm having some problems with execution.

If I run the following code:

declare @userid numeric(18,0);
declare @username nvarchar(max);
set @userid = 400
execute GetUserNameFromID @userid,@username output
select @username

which calls the following stored procedure:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE GetUserNameFromID 
    -- Add the parameters for the stored procedure here
    @UserID numeric(18,0),
    @UserName nvarchar(MAX) OUT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT @UserName = u.name from Users u where ID=@UserID
END
GO

I get a nice result 'sometestuser'

But when calling it from my trigger it fails to return a value from the stored procedure:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Trigger [dbo].[CheckIfUserHasNoItemsLeft] on [dbo].[Items] for update
As

Begin
set nocount on 

declare @inactive_user nvarchar(50);
declare @userid numeric(18,0);
declare @username nvarchar(MAX);

if update(InactiveUser)
    set @inactive_user = (select InactiveUser from inserted)
    if @inactive_user <> null
        set @userid = (select CID from inserted)
        execute GetuserNameFromID @userid,@username output
        if @username <> null        
            insert into tasks (Task) values ('The last item for ' + @username + ' has been marked inactive, check if this user should now be also marked inactive.')
End

InactiveUser is the name of the app user who has marked this item inactive, it is what I am using as a check to see if the item has been set inactive rather than create an additional boolean column just for this purpose.

I'm sure it's something simple but information on If...Then statements for SQL seems to be limited and a lot of answers suggest using Case but the query editor gives me errors about incorrect syntax no matter which way I try to do it that way.

As I'm learning I'm more than happy for someone to show me a completely new way of handling this if what I've done is wrong or bad design. I'm hoping to create a set of triggers that will add items to the tasks table for administrators to check when user accounts appear to be stale and other maintenance checks etc.

I am using SQL server 2005.

Thank you.

Aucun commentaire:

Enregistrer un commentaire