trigger did not not trigger as expected

[yan19454]

whenever thd quiz log has quiz enter which the quiz name in table tblInternalEduModule category is Win add to the tblCurrentWinTrainingLog

the trigger did not trigger as expected ? Thx.





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') ) !=@@rowcount
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 ON i.[Quiz Name] = tblInternalEduModule.[Quiz]

end

here is the my table
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Quiz Log]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Quiz Log]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblInternalEduModule]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblInternalEduModule]
GO

CREATE TABLE [dbo].[Quiz Log] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Employee Name] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DOB] [nchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Quiz Name] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Access Quiz Time] [datetime] NULL ,
[Score] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Manager Login ID] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblInternalEduModule] (
[ModuleID] [int] NULL ,
[Category] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CourseName] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Quiz] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RequriedBy] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[HREMP_adp]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[HREMP_adp]
GO

CREATE TABLE [dbo].[HREMP_adp] (
[SSN] [nvarchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EMPLNO] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FULL NAME] [nvarchar] (26) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FNAME] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LNAME] [nvarchar] (26) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MI] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[JOBTITLE] [nvarchar] (26) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EMPSTATUS] [float] NULL ,
[DOB] [smalldatetime] NULL ,
[ENTITY] [nvarchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CC] [nvarchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CCNAME] [nvarchar] (31) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HIRE_DATE] [smalldatetime] NULL ,
[PHONE] [nvarchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[STREET] [nvarchar] (31) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CITY] [nvarchar] (31) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[STATE] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ZIPCODE] [nvarchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TERMDATE] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

 

[JarlH]

You could try the MS SQL Server forum instead, they'll probably be able to help you there. (This hasn't got much to do with ANSI SQL.)

 

[yan19454]

Thanks. I figure that out right now. Thanks.

if (SELECT COUNT(*)
FROM inserted INNER JOIN
tblInternalEduModule ON [inserted].[Quiz Name] = tblInternalEduModule.Quiz
WHERE (tblInternalEduModule.Category = 'WIN') ) >0