T-SQL Insert Trigger to insert,update on if condition on multiple tables

Question

I'm having a few issues trying to resolve an SQL Trigger to automatically set a user to blocked & create the block record including date in another table, if their Due date is equal to a set date.

The issue is that when the trigger is set off by an insert, the print statements are executed and the insert occurs, but the insert into the table does not, or the update statement ? Can anyone explain why ?

Note: Both the insert and the Update statement are fine when executed by themselves.

ACCOUNT TABLE

CREATE TABLE [dbo].[Account](
[AccountNo] [int] IDENTITY(1,1) NOT NULL,
[CustomerNo] [int] NOT NULL,
[PaymentNo] [int] NULL,
[CreditNo] [int] NULL,
[BlockID] [dbo].[number] NULL,
[Balence] [dbo].[currency] NOT NULL,
[AmountDue] [dbo].[currency] NOT NULL,
[DueDate] [dbo].[dates] NULL,
[AutherisedBy] [nvarchar](50) NOT NULL,
[DateCreated] [date] NOT NULL,

BLOCKEDUSER TABLE

CREATE TABLE [dbo].[BlockedUsers](
[BlockID] [int] IDENTITY(1,1) NOT NULL,
[DateEnforced] [dbo].[dates] NOT NULL,
[Blocked] [dbo].[switch] NOT NULL,

TRIGGER

ALTER TRIGGER [dbo].[Add_Blocked_User]
ON [dbo].[Account]
FOR INSERT
AS  
BEGIN
SET NOCOUNT ON;

Declare @ID int
Select @ID = [AccountNo] from inserted  
If(Select [DueDate] from inserted) = '2011-01-01'

INSERT INTO dbo.BlockedUsers(DateEnforced,Blocked)
VALUES (GETDATE(),1)
PRINT 'New Block Date Added'

UPDATE Account 
Set BlockID = IDENT_CURRENT('BlockID')
where @ID = @ID
PRINT 'Account Blocked'

END

GO

Fully Working Example : Completed using Help Below.

ALTER TRIGGER [dbo].[Add_Blocked_User]
ON [dbo].[Account]
AFTER INSERT
AS 
BEGIN

SET NOCOUNT ON;

Declare @ID int
Select @ID = [AccountNo] from inserted  
 If(Select [DueDate] from inserted)Not Between (select CONVERT(date, getdate() - 30)) And (select CONVERT(date, getdate()))
Begin
    INSERT INTO dbo.BlockedUsers(DateEnforced,Blocked)
    VALUES (GETDATE(),1)
    PRINT 'New Block Date Added'

    UPDATE Account 
    Set BlockID = (Select Max(BlockID) From BlockedUsers)
    where [AccountNo] = (Select [AccountNo] from inserted)  
    PRINT 'Account Blocked'
 End

 END

GO

Solution

The IF statement in Transact-SQL expects a single statement after the condition:

IF condition
  statement;

If you want to perform more than one statement in the same branch, you must enclose them in the BEGIN/END "brackets":

IF condition
BEGIN
  statement;
  statement;
  ...
END;

In your trigger, only the INSERT statement executes depending on the result of the (Select [DueDate] from inserted) = '2011-01-01' condition. As for both PRINTs and the UPDATE, they execute unconditionally, i.e. after every insert into Account. So, you probably need to add BEGIN and END around INSERT, UPDATE and both PRINTs:

...
If(Select [DueDate] from inserted) = '2011-01-01'
BEGIN
INSERT INTO dbo.BlockedUsers(DateEnforced,Blocked)
VALUES (GETDATE(),1);
PRINT 'New Block Date Added';

UPDATE Account 
Set BlockID = IDENT_CURRENT('BlockID')
where @ID = @ID;
PRINT 'Account Blocked';
END;
...

Comments

[Andriy M]

Your trigger will break when two or more rows get inserted with a single statement.

[Andriy M]

FOR INSERT and AFTER INSERT mean the same in Transact-SQL. It is INSTEAD OF INSERT that replaces the actual operation.

[Baggerz]

Thankyou Andy, This helped me get both update and insert working correctly. Really appreciated.

[Baggerz]

Thankyou Andy, This helped me get my if condition working correctly which Ive now extended to use system sates to block only if due date is >30 days old. Kudos, Really appreciated.

[Brian White]

Sorry about that :( I never use FOR. It was the fact that update account didn't do anything that made me think it as acting as instead of.

[Brian White]

Why? I answered, it turned out I was wrong, I got no upvotes.

[Racil Hilan]

Why? Because it is wrong, cluttering the space, and wasting others' time. Add to that: so you don't get down-votes. Others reading your answer need to read all the comments just to find it is wrong. I will not down-vote it for now, but I suggest deleting your answer as it is not salvageable, before you start getting down-votes.

[Brian White]

I don't have an option to delete it. I answered, I was wrong, it happens.

[Lachlan Ennis]

I have down voted this so it doesn't show in the answers. I suggest you update it to avoid confusion for people in the future.