0

I have a trigger setup below which I have 2 questions on.

  1. does the code look sound?

  2. How do I nest another if exist inside the update. Essentially I want it to capture another column depending on if the component name is 'x' or 'y'

Specifically if the name is 'x' I want to update JobStart = jr.start and if name is 'y' I want to update JobEnd = jr.end

ALTER TRIGGER [dbo].[T_trigger_IU]
   ON  [dbo].[tableA]
   AFTER INSERT, UPDATE
AS 
BEGIN TRAN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for trigger here
    IF EXISTS (select * from inserted i
                inner join jobmst jm on jm.jm_id = i.jm_id
                inner join [db2].[dbo].[table2] dm on dm.ID = i.jr_id
                where i.jr_owner = 913 and jm.jm_name LIKE '%.%' and jm.jm_name NOT LIKE '.%')
        BEGIN           
        UPDATE [db2].[dbo].[table2]
            SET RefreshTm = GETDATE(),
                StatID = i.jr_status,
                ComponentID = jrchild.jr_id,
                ComponentName = jmchild.jm_name,
                ComponentStatus = jrchild.jr_status
            FROM inserted i
            inner join jobrun jr on jr.jr_id = i.jr_id
            inner join jobrun jrchild on jrchild.jr_prntid = i.jr_id
            inner join jobmst jmchild on jmchild.jm_prntid = i.jm_id
            inner join [db2].[dbo].[table2] dm on dm.ID = i.jobrun_id
            where i.jr_id = dm.ID
        END
    ELSE
        BEGIN
        INSERT INTO [db2].[dbo].[table2]
            (CreateTm, Date, Name, DefID, Cal, CalID,
            PStart, PEnd, ID, StatID)
        SELECT
            GETDATE(), i.jr_proddt, jm.jm_name, i.jm_id, em.em_name, em.em_id,
            jd.jd_fromtm, jd.jd_untiltm, i.jr_id, i.jr_status
            FROM jobrun jr
            inner join inserted i on i.jr_id = jr.jr_id
            inner join jobmst jm on jm.jm_id = i.jm_id
            inner join evntmst em on em.em_id = jm.em_id
            inner join jobdtl jd on jd.jd_id = jm.jd_id
            where i.jr_owner = 913 and jm.jm_name LIKE '%.%' and jm.jm_name NOT LIKE '.%'
        END
COMMIT TRAN
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
whoisearth
  • 4,080
  • 13
  • 62
  • 130
  • 1
    For starters: I personally prefer to have two separate trigger - one for each operation - instead of having to do a lot of checking to decide which code path to use. Secondly: **don't** use `BEGIN TRAN`...`COMMIT TRAN` inside a trigger! The trigger already runs in the *ambient transaction* that the statement that causes this trigger to fire has started. – marc_s Apr 11 '14 at 05:01
  • So If I'm reading this correct you'd suggest 1 trigger for an if exists update and 1 trigger for an if not exists insert? The begin tran I got directly from SO here - http://stackoverflow.com/questions/108403/solutions-for-insert-or-update-on-sql-server as the reasoning changed? My fear is to do what I want to do going by this method I'll have 50+ triggers but yes, will be better to isolate the bad ones, but is it not better to contain the entire process inside one trigger to limit database lookups? – whoisearth Apr 11 '14 at 13:32
  • Yes, I would have one trigger for `UPDATE`, and a separate one for `INSERT` - then you know ahead of time that you're dealing with updates and/or inserts - and you don't need a lot of checking code. The reasoning on transactions hasn't changed - you just probably misinterpreted those answers - they weren't dealing with **triggers** - in a trigger, you're already inside the *ambient* transaction of the statement that caused the trigger to fire - let that transaction do its work – marc_s Apr 11 '14 at 13:58
  • ok a bit clearer and makes sense and frankly I like the keeping code separate so I'm not going to complain. The problem simply becomes having multiple triggers to filter through so I'll have to hammer down how to name them effectively. – whoisearth Apr 11 '14 at 14:11
  • This works perfectly thnx. Now my holdup isn't how to link all the triggers into 1 trigger just how to name them so it's organized properly :) – whoisearth Apr 11 '14 at 15:26

0 Answers0