First time I need to use triggers. I have a very simple table with no primary key. It has only one row always. One column holds a value for the "current period".
The goal is whenever the value changes, a stored procedure should be executed with the new value of "current period" passed as a parameter to the stored procedure. Simple as that :).
I tried the following:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[CCPM_CLIENT_STATUS_PERIOD_CHANGE]
ON [dbo].[Current_Period]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
IF UPDATE (Current_Rep_Period)
BEGIN
DECLARE @crp CHAR(6)
SET @crp = (SELECT i.Current_Rep_Period FROM inserted i)
EXEC [dbo].[RS_L1_CCPM_CLIENT_STATUS_CREATION] @crp
END
END
No luck :)
It is my mistake. The trigger actually works even without primary key there is only one row (otherwise you really need a primary key to join with the deleted table). The problem is that I did not realized at the moment the triggers are synchronous and the commit of the update does not happen until the called stored procedure exits. When you update the table graphically in management studio by typing the new value you get a timeout error which is a default behavior of ssms ui. If I update using t-sql it works by committing the update when the actions in the trigger are completed. So actually the trigger worked. I need now to find a solution to make it asynchronous. Thank you all for your comments
