checking whether a trigger executed successfully

[FALCONSEYE]

is there a way to check if an update/insert trigger executes successfully? for some reason, it seems like my trigger sometimes works and sometimes doesn't. is there a log file that records if the execution failed?
thanks in advance

http://www.jetansolutions.com

 

[vongrunt]

How 'bout posting trigger code here?

------
Theory: everybody knows everything, nothing works
Practice: everything works, nobody knows why

 

[SQLDenis]

you can check for @@Error in the trigger itself for errors and writing suser_sname, ID, getdate() etc etc to an errorlogtable
also you can have an if condition by checking the @@rowcount after your trigger action and writing those id's to a table so that you can investigate

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[FALCONSEYE]

this is the trigger

CREATE TRIGGER customerOptStatus
ON Customer
FOR INSERT, UPDATE

AS

declare @email varchar(100)

BEGIN

IF EXISTS
(
SELECT i.emailAddress
FROM Inserted i, Customer c
WHERE i.emailAddress = c.emailAddress
AND c.optInCD = 'no'
AND i.optInCD = 'yes'
)
BEGIN

SELECT @email = emailAddress FROM Inserted

UPDATE Customer
SET optStatus = 'NO'
WHERE emailAddress = @email
END

END

http://www.jetansolutions.com

 

[SQLDenis]

one way

CREATE TRIGGER customerOptStatus
ON Customer
FOR INSERT, UPDATE

AS 

declare @email varchar(100)
declare @errorId int,@Rowcount int
BEGIN

    IF EXISTS 
    (
        SELECT i.emailAddress 
        FROM Inserted i, Customer c
        WHERE i.emailAddress = c.emailAddress
        AND   c.optInCD = 'no'
                AND   i.optInCD = 'yes'
    )
    BEGIN
        
        SELECT @email = emailAddress FROM Inserted

        UPDATE Customer
        SET optStatus = 'NO'
        WHERE emailAddress = @email
	select @errorId=@@error,@Rowcount =@@ROWCOUNT
	if @errorId <> 0
	begin
		insert into errorlogtable
		select 'some error has occured email logged =' + @email,getdate(),suser_sname,@email
	end
	else
	if @Rowcount =0
	begin
		insert into errorlogtable
		select 'No records have been updated fro some bizzare reason email logged =' + @email,getdate(),suser_sname,@email
	end
    END
    
END

of course you will need to create the errorlogtable table

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

Are multirow inserts/updates possible or not?

------
Theory: everybody knows everything, nothing works
Practice: everything works, nobody knows why

 

[SQLDenis]

For multirow you can't use the @email parameter in that case (join with inserted table instead), this might be the cause of the trouble BTW

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[FALCONSEYE]

updates barely happen, and inserts are one at a time.

http://www.jetansolutions.com

 

[FALCONSEYE]

thanks SQLDenis, i will give it a try...

http://www.jetansolutions.com

 

[SQLDenis]

Hold on like vongrunt said your problem might be with multi row updates in that case you have to change

UPDATE Customer
        SET optStatus = 'NO'
        WHERE emailAddress = @email

to

UPDATE c
        SET c.ptStatus = 'NO'
from Customer c 
join Inserted i on i.emailAddress = c.emailAddress
AND   c.optInCD = 'no'
AND   i.optInCD = 'yes'

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/