CODEWITHSUNDEEP
sqlsql-servertriggers
mHelpMe
mHelpMe

Reputation: 6668

instead of trigger result output

I have created my first trigger. Please see the code section for the trigger below.

The triggers and the results are as expected, except for one thing.

So when I run the code below it will not insert the values into my table so the number of records remains unchanged.

insert into MatlabSearchPath(directory, userName)
values('madeup', 'default')

In the messages window though I get two lines. I don't understand why I see two lines and in particular 1 row affected - the number of records in my table hasn't changed?

(0 row(s) affected)
(1 row(s) affected)

Trigger

create trigger trDefaultPathInsert on DVLP_QES.dbo.MatlabSearchPath
instead of insert
as
begin
declare @defCount int
declare @retVal int
select @defCount = count(userName) from inserted where userName = 'Default'

if (@defCount > 0)
begin
    select @retVal = count(HostName) from DVLP_QES.dbo.UserHostName where HostName = HOST_NAME()
    if (@retVal > 0)
    begin           
        insert into MatlabSearchPath select * from inserted
    end
    else
    begin
        insert into MatlabSearchPath select * from inserted where inserted.userName <> 'Default'
    end 
end

end

Update

I should mention that there a 3 triggers on this table, one is the trigger above the other one is a delete & the last one is an update

Upvotes: 2

Views: 205

Answers (2)

Juan
Juan

Reputation: 1382

The situation here if the first message indicating cero is because the instead of trigger is uses to ignore the insert you sent and do instead whats in the trigger

You can debug your code with management studio

Upvotes: 0

OzrenTkalcecKrznaric
OzrenTkalcecKrznaric

Reputation: 5646

Your trigger does the following:

  1. Counts records you are trying to insert, where userName equals 'Default' In your case, count is 1. Pay attention to your collation - if it's case sensitive, you are going to skip that whole branch of code.

  2. If you enter the if branch, next thing trigger checks is if there are rows in UserHostName table where HostName equals host name of your client; pay attention that you don't think it should be host name of your server or something like that

  3. If you enter the TRUE-branch, it should insert everything to the table; however, if not, it shouldn't insert anything. Of course, except if the collation is case sensitive, then revert the logic.

I I were you, I would add PRINT statements into trigger, just to make sure how does it execute.

create trigger trDefaultPathInsert on DVLP_QES.dbo.MatlabSearchPath
instead of insert
as
begin
declare @defCount int
declare @retVal int
select @defCount = count(userName) from inserted where userName = 'Default'

PRINT '@defCount'
PRINT @defCount

if (@defCount > 0)
begin
    select @retVal = count(HostName) from DVLP_QES.dbo.UserHostName where HostName = HOST_NAME()

    PRINT '@retVal'
    PRINT @retVal 

    if (@retVal > 0)
    begin           
        PRINT 'TRUE-BRANCH'
        insert into MatlabSearchPath select * from inserted
    end
    else
    begin
        PRINT 'FALSE-BRANCH'
        insert into MatlabSearchPath select * from inserted where inserted.userName <> 'Default'
    end 
end

EDIT

It seems that the message about rows affected can't be controlled inside the trigger. Even the standard SET NOCOUNT ON on the trigger beginning won't stop it from showing. This gave me notion that the message is a result of the trigger being successfully finished by calling it with X rows, where X will eventually be in the X row(s) affected message.

This SO question furtherly confirms the problem.

Upvotes: 1

Related Questions