CODEWITHSUNDEEP
sql-serversql-server-2008triggers
user1292656
user1292656

Reputation: 2560

How can I know that a Trigger is fired or not?

I am using SQL Server 2008 R2 on a windows 7 machine. I have a trigger that should be fired on an insert but unfortunately it does not. I do not have the SQL Profiler because I have an express edition. Is there any other way to see what is going wrong?. The insert into the table teams is done with the Import Wizard of SQL Server, an i import from .CSV into a table.

CREATE TRIGGER teams.process ON teams
AFTER INSERT
AS
BEGIN
    DECLARE @homeTeamId INT
    DECLARE @awayTeamId INT
    DECLARE @maxTeamId INT
    DECLARE @matchId INT

    SELECT @maxTeamId = 0
    SELECT @maxTeamId = ISNULL(MAX(teamId), 0) from tblTeams

    --- Check if home team has already been inserted into the table.
    SELECT @homeTeamId = -1
    SELECT 
        @homeTeamId = teamId 
    FROM 
        tblTeams t
        JOIN inserted i
        ON t.teamName = i.hometeam
    IF (@homeTeamId = -1) 
    BEGIN
        SELECT @homeTeamId = @maxTeamId + 1
        SELECT @maxTeamId = @maxTeamId + 1
        INSERT INTO tblTeams SELECT @homeTeamId, i.hometeam FROM inserted i
    END

    --- Check if away team has already been inserted into the table.
    SELECT @awayTeamId = -1
    SELECT 
        @awayTeamId = teamId 
    FROM 
        tblTeams t
        JOIN inserted i
        ON t.teamName = i.awayteam
    IF (@awayTeamId = -1) 
    BEGIN
        SELECT @awayTeamId = @maxTeamId + 1
        SELECT @maxTeamId = @maxTeamId + 1
        INSERT INTO tblTeams SELECT @awayTeamId, i.awayteam FROM inserted i
    END

    -- insert a record into the matches table with the home team ID and away team ID.
    SELECT @matchId = 0
    SELECT @matchId = ISNULL(MAX(MatchId), 0) FROM tblMatches
    INSERT INTO tblMatches 
    SELECT @matchId + 1, @homeTeamId, @awayTeamId, i.score 
    FROM inserted i
END

Upvotes: 2

Views: 10891

Answers (2)

datagod
datagod

Reputation: 1051

Bulk insert wizards / data import wizards usually bypass triggers on the destination table.

Upvotes: 1

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239646

Okay. If we can change the table definitions of tblTeams and tblMatches slightly, so that they maintain their own ID columns using IDENTITY, then we can fix the trigger to be safe for multi-row inserts:

create table teams (
    hometeam varchar(10) not null,
    awayteam varchar(10) not null,
    score int not null
)
create table tblteams (
    teamId int IDENTITY(1,1) not null,
    teamName varchar(10) not null
)
create table tblmatches (
    matchId int IDENTITY(1,1) not null,
    HomeTeamID int not null,
    AwayTeamID int not null,
    Score int not null
)
go
CREATE TRIGGER process ON teams
AFTER INSERT
AS
SET NOCOUNT ON
declare @TeamIDs table (TeamID int not null,TeamName varchar(10) not null)

;with AllTeams as (
    select hometeam as teamName from inserted
    union
    select awayteam from inserted
)
merge into tblTeams tt using AllTeams at on tt.teamName = at.teamName
when matched then update set teamName = at.teamName
when not matched then insert (teamName) values (at.teamName)
output inserted.TeamID,inserted.teamName into @TeamIDs;

insert into tblmatches (HomeTeamID,AwayTeamID,Score)
select ht.TeamID,at.TeamID,i.Score
from inserted i
inner join @TeamIDs ht on i.hometeam = ht.TeamName
inner join @TeamIDs at on i.awayteam = at.TeamName
GO

And then we test it out:

insert into teams (hometeam,awayteam,score) values
('abc','def',10),
('def','ghi',5),
('jkl','mno',7)
go
insert into teams (hometeam,awayteam,score) values
('abc','ghi',19),
('pqr','stu',11)
go
select * from tblteams
select * from tblmatches

The issue with your existing trigger is it didn't cope with inserted containing multiple rows - the trigger is fired once per statement, not once per row. So e.g. these line are wrong:

SELECT @homeTeamId = @maxTeamId + 1
    SELECT @maxTeamId = @maxTeamId + 1
    INSERT INTO tblTeams SELECT @homeTeamId, i.hometeam FROM inserted i

Since there might be multiple homeTeam values to deal with.

It also didn't deal well with concurrency - two calls to the trigger happening in parallel might end up with the same @maxTeamId value - and then attempt to insert rows into tblTeam with the same TeamId values - whereas using IDENTITY columns, SQL Server deals with this for us automatically.

The only slight fudge in the above is using MERGE to insert new teams - the WHEN MATCHED line will do a No-Op UPDATE for existing rows (since we know the teamName matches on both sides), but it's a nice trick to do the lookup of existing rows and INSERT of new rows in a single statement.

I've just realised that you've said that you're using the Import data wizard. I have a feeling that the SSIS Package that it generates creates a destination using Fast Load, and doesn't specify FIRE_TRIGGERS. That could scupper you also.

You could either generate the package using the wizard, then edit the properties, or use the wizard to insert into a staging table, then do a plain INSERT/SELECT from that table into our teams table, and let the trigger fire that way.

Upvotes: 1

Related Questions