I have a trigger setup below which I have 2 questions on.
does the code look sound?
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