trigger inner join problem

Question

Post reply

trigger inner join problem

Frances L

SSCarpal Tunnel

Points: 4727
More actions
January 5, 2007 at 7:52 am

#98026

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 mine 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
January 8, 2007 at 8:00 am

This was removed by the editor as SPAM

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

if (SELECT COUNT(*)

FROM inserted INNER JOIN

tblInternalEduModule ON [inserted].[Quiz Name] = tblInternalEduModule.Quiz

WHERE (tblInternalEduModule.Category = 'WIN') ) !=@@rowcount

that looks invalid to me. select count(*) from inserted will set @@rowcount to 1 row. you should not use @@rowcount in a statement...

you should execute a command, save @@rowcount to a local variable, and then compare against the local variable.

ie declare @rowsaffected int

set rowsaffected =@@rowcount

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Frances L SSCarpal Tunnel Points: 4727 More actions · Answer 2

Thx. I changed the as

if (SELECT COUNT(*)

FROM inserted INNER JOIN

tblInternalEduModule ON [inserted].[Quiz Name] = tblInternalEduModule.Quiz

WHERE (tblInternalEduModule.Category = 'WIN') ) >0 . it work fine right n ow.