Geek Answers Handbook

T-SQL Insert Trigger for inserting, updating if condition for multiple tables

I have several problems trying to resolve an SQL trigger in order to automatically set the user to lock and create a block record, including the date in another table, if their maturity date is equal to the set date.

The problem is that when the trigger is disabled by insertion, the print instructions are executed and the insertion occurs, but is there no insertion in the table or an update statement? Can someone explain why?

Note. The insert and update statements work fine when executed.

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

A fully working example: completed using the 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
+7
source share
2 answers

An IF in Transact-SQL expects one statement after a condition:

 IF condition statement ;

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

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

Only the INSERT statement is executed in your trigger, depending on the result of the condition (Select [DueDate] from inserted) = '2011-01-01' . As for both PRINT and UPDATE, they are executed unconditionally, i.e. After each insertion into Account . So you probably need to add BEGIN and END around INSERT, UPDATE and both PRINT:

 ... 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 ; ...
+5
source

you did it

 FOR INSERT

Do you want to use

 AFTER INSERT, UPDATE

FOR INSERT tells the Sql server that your trigger will completely replace the regular insert operation. AFTER INSERT tells Sql Server to continue and insert the line, and then execute this code as a post-processing step.

-one
source

More articles:


All Articles