I am checking some data prior to insert using an instead of trigger. The trigger checks to see if a person being inserted has specific status ( bool(bit) isStaffManager=1) in the Person table. If bit = 0 an exception should be
thrown. For some reason the trigger seems to try to insert before raising my exception and throws a null exception when @Manager is unassigned. If I assign a value, even an invalid one, to the variable it works OK. I don't understand why.
From the code below if I remove the line "Set @Manager = '; (marked near the end of the trigger code) I get the error "can't insert null into manager..."
If I put the line in it works OK- Raises my exception with message - "Manager Required...". Why is the trigger attempting an insert instead of throwing the exception? And why does the line this assignment fix this?
CREATE TABLE [dbo].[Job](
[Job] [varchar](64) NOT NULL,
[Description] [varchar](max) NOT NULL,
[Manager] [varchar](64) NOT NULL,
[ProjectCode] [varchar](6) NOT NULL Default 0,
[ExecptionSourceTitle] [varchar](64) NOT NULL Default ',
CONSTRAINT [PK_Project] PRIMARY KEY CLUSTERED
) ON [PRIMARY]
CREATE TABLE [dbo].[Person](
[Person] [varchar](64) NOT NULL,
View Complete Post