Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
add comment

2 Answers

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:

CREATE TRIGGER [dbo].[marriage] ON  [dbo].[applicant_personal_info] FOR INSERT
AS 
BEGIN
    SET NOCOUNT ON
    IF EXISTS (SELECT [marital_status] FROM INSERTED WHERE marital_status = 'married')
    BEGIN
        INSERT INTO [dbo].[applicant_marriage_info] ([dom])
        SELECT
            'abc' --Replace with whatever you're really inserting
        FROM INSERTED
        WHERE marital_status = 'married'
    END
END
share|improve this answer
add comment

So this trigger is actually pretty close to what you need. Now the problem here is that only one row will be inserted into applicant_marriage_info if there's any rows in inserted. Is this what you actually want? What happens if there's multiple rows inserted at once?

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;
INSERT INTO [dbo].[applicant_marriage_info]([dom])
SELECT 'abc' 
FROM inserted 
WHERE marital_status = 'married'

You'll probably need more columns than what I've shown.

share|improve this answer
    
It should be done every time this data is entered –  kabuto178 Apr 15 '13 at 4:11
2  
Then one way is to replace the last part with BEGIN INSERT INTO [dbo].[applicant_marriage_info]([dom]) SELECT 'abc' FROM inserted WHERE marital_status = 'married' END –  ypercube Apr 15 '13 at 7:04
    
I've changed the sample code a bit to account for this. You don't need the IF EXISTS bit any more. Just run the query against inserted and load that data into the other table. You'll probably need more columns than I've got shown, some sort of ID probably, but you probably get the idea. –  mrdenny Apr 15 '13 at 17:54
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.