trigger did not work.
-
CREATE trigger CurrentWinModuleInsert
on dbo.[Module Log]
for insert as
--insert on the win module Back Injury Competency only i
if (SELECT COUNT(*)
FROM inserted
WHERE ([inserted].[Module Name]='Back Injury Competency') ) >0
begin
insert into tblCurrentWinTrainingLog(EmplNO,CTDate, QuizName)
select HREMP_adp.EMPLNO, i.[Access Module Time] AS CTDate,i.[Module Name] as QuizName
FROM inserted i INNER JOIN
HREMP_adp ON i.[Employee Name] = HREMP_adp.[FULL NAME] AND i.DOB = HREMP_adp.DOB
end
I tested that insert. It worked. I have another trigger set up on the similar table. it work. too.
CREATE trigger CurrentWinInsert
on [Quiz Log]
for insert as
--insert on the win quiz only is win and
if (SELECT COUNT(*)
FROM inserted INNER JOIN
tblInternalEduModule ON [inserted].[Quiz Name] = tblInternalEduModule.Quiz
WHERE (tblInternalEduModule.Category = 'WIN') ) >0
begin
insert into tblCurrentWinTrainingLog(EmplNO,CTDate, QuizName,Score)
select HREMP_adp.EMPLNO, i.[Access Quiz Time] AS CTDate,i.[Quiz Name] as QUizName, i.Score
FROM inserted i INNER JOIN
HREMP_adp ON i.[Employee Name] = HREMP_adp.[FULL NAME] AND i.DOB = HREMP_adp.DOB
INNER JOIN
tblInternalEduModule d ON i.[Quiz Name] = d.[Quiz]
end
this one is working. I just do not understand why set on module log did not work.
Thx.
-
If the INSERT worked standalone, maybe the IF statement is "iffy" ?
Speaking of which, you never need to COUNT() an entire resultset, if all you care about is that the count is greater than zero. Think efficiency. Use EXISTS.
CREATE trigger CurrentWinModuleInsert
on dbo.[Module Log] for insert as
--insert on the win module Back Injury Competency only i
if Exists (Select * FROM inserted
Where [Module Name] = 'Back Injury Competency' )
begin
Insert Into tblCurrentWinTrainingLog
(EmplNO,CTDate, QuizName)
Select
h.EMPLNO, i.[Access Module Time] AS CTDate, i.[Module Name] as QuizName
FROM inserted As i
INNER JOIN HREMP_adp As h
ON i.[Employee Name] = h.[FULL NAME] AND
i.DOB = h.DOB
end
If that still "doesn't work", maybe it's the join ? Try it as a LEFT join to HREMP_adp to debug whether a failed join expression is causing no records to be inserted ?
-
this one is working with similiar syntax. It seems it did not consistently. Today I checked the data, just find that out. but I tested. It work well.
CREATE trigger CurrentWinInsert
on [Quiz Log]
for insert as
--insert on the win quiz only is win and
if (SELECT COUNT(*)
FROM inserted INNER JOIN
tblInternalEduModule ON [inserted].[Quiz Name] = tblInternalEduModule.Quiz
WHERE (tblInternalEduModule.Category = 'WIN') ) >0
begin
insert into tblCurrentWinTrainingLog(EmplNO,CTDate, QuizName,Score)
select HREMP_adp.EMPLNO, i.[Access Quiz Time] AS CTDate,i.[Quiz Name] as QUizName, i.Score
FROM inserted i INNER JOIN
HREMP_adp ON i.[Employee Name] = HREMP_adp.[FULL NAME] AND i.DOB = HREMP_adp.DOB
INNER JOIN
tblInternalEduModule d ON i.[Quiz Name] = d.[Quiz]
end
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply