I want this trigger to be fired after an insert is made with the text married
on the marital_status
column, this is what I have so far
ALTER TRIGGER [dbo].[marriage]
ON [dbo].[applicant_personal_info]
AFTER INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
IF (SELECT [marital_status] FROM inserted) = 'married'
BEGIN
INSERT INTO [dbo].[applicant_marriage_info]([dom])
VALUES('abc')
END
END
Best Answer
The issue you'll run into here comes from the fact that SQL Server doesn't have the "FOR EACH ROW" triggers that Oracle does. You have to write your triggers to handle multi-row changes, i.e. the INSERTED or DELETED virtual tables can have more than one row in them.
If such an update were to happen, your trigger would fail, since
(SELECT [marital_status] FROM inserted)
would return multiple rows, and subqueries have to return a single value to be used in a direct comparison.The trigger would probably have to look something like this: