Triggers on Application database disrupt the application.

[FatalExceptionError]

I have some triggers on two tables in an application database in our dev environment. The triggers are used to track changes to certain fields on those tables for compliance and audit reports. Long story short it appears that the triggers are causing the application to have an error when an update is made in the application pertaining to those very tables. Disabling the triggers causes the application to work correctly.

There are 3 triggers on 2 tables. One trigger each for insert, update, delete.

Has anyone encounterd anything like this?

=======================================
The statement below is true
The statement above is false

There are 10 kinds of people, those who know binary and those who don't

 

[monksnake]

There are 10 kinds of people, those who know binary and those who don't

What about the other 8 kinds of people??



<.

 

[SQLSister]

More than likely the triggers have an error. THe most common error is that they do not take into consideration that the delted and/or inserted tables may contain multiple records.

For more specifics, post the error and the trigger code

Questions about posting. See faq183-874

 

[SQLDenis]

Let me guess?

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

Am I right? Go ahead admit it ;-)

Yor trigger is not coded for multi row statements and fails miserably when someone updates or inserts more than 1 row

lookup triggers - Multirow Considerations in BOL


Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[SQLSister]

Dennis, I see great minds think alike

Questions about posting. See faq183-874

 

[FatalExceptionError]

The problem comes up on an insert of a new customer in this particular instance so I am not sure if it is a multi row but I don't think it would be returned in the error log because of the the line right after the first comment.

After the trigger was disabled the test customer went in and only 1 row was added.

I wish I could get a solid error code from the application log file. Sql server error logs don't indicate anything
but
SqlError : Error number 100 (2714)
SqlError : Error number 100 (5701)

Profiler shows me the app uses alot of the sp_cursorxxx procs. The only sql server error I can find is.

[Microsoft][ODBC SQL Server Driver][SQL Server]There is already an object named 'QX__$SCHEMA' in the database.

This table holds table_name, column_name and 2 columns of application codes.

I was under the impression triggers operation would be independent of the application.

This issue also pops up in an edit of security settings but I will tackle that after this one because I am sure they are related.

Update Trigger

CREATE TRIGGER customer_trigger
   ON  customer
   FOR INSERT
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @cust_code char(6)
	DECLARE @name_old char(35)
	DECLARE @adr1_old char(35)
	DECLARE @adr2_old char(35)
	DECLARE @adr3_old char(35)
	DECLARE @city_old char(30)
	DECLARE @state_old char(2)
	DECLARE @zip_old char(15)
	DECLARE @country_old char(3)
	DECLARE @name_new char(35)
	DECLARE @adr1_new char(35)
	DECLARE @adr2_new char(35)
	DECLARE @adr3_new char(35)
	DECLARE @city_new char(30)
	DECLARE @state_new char(2)
	DECLARE @zip_new char(15)
	DECLARE @country_new char(3)
	DECLARE @user char(10)
	
	SELECT
		@cust_code = cust_code,
		@name_new = cust_name, 
		@adr1_new = cust_adr1,
		@adr2_new = cust_adr2,
		@adr3_new = cust_adr3,
		@city_new = cust_city,
		@state_new = cust_state,
		@zip_new = cust_zip,
		@country_new = cust_country,
		@user = user_code
	FROM INSERTED
	
	INSERT INTO Triggered_customer 
		(cust_code, cust_name_before, cust_adr1_before, cust_adr2_before, cust_adr3_before,
		cust_city_before, cust_state_before, cust_zip_before, cust_country_before,
		cust_name_after, cust_adr1_after, cust_adr2_after, cust_adr3_after,
		cust_city_after, cust_state_after, cust_zip_after, cust_country_after,
		user_code, TIMESTAMP_DTM, TRIGGER_OPERATION_CD)
	VALUES
		(@cust_code, @name_old, @adr1_old, @adr2_old, @adr3_old, @city_old, @state_old,
		@zip_old, @country_old, @name_new, @adr2_new, @adr2_new, @adr3_new, @city_new,
		@state_new, @zip_new, @country_new, @user, GetDate(), 'I')

END

P.S. I was not the original trigger writer.

=======================================
The statement below is true
The statement above is false

There are 10 kinds of people, those who know binary and those who don't

 

[SQLDenis]

This is your problem

SELECT
        @cust_code = cust_code,
        @name_new = cust_name, 
        @adr1_new = cust_adr1,
        @adr2_new = cust_adr2,
        @adr3_new = cust_adr3,
        @city_new = cust_city,
        @state_new = cust_state,
        @zip_new = cust_zip,
        @country_new = cust_country,
        @user = user_code
    FROM INSERTED

this is what you need

CREATE TRIGGER customer_trigger
   ON  customer
   FOR INSERT
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE @cust_code char(6)
    DECLARE @name_old char(35)
    DECLARE @adr1_old char(35)
    DECLARE @adr2_old char(35)
    DECLARE @adr3_old char(35)
    DECLARE @city_old char(30)
    DECLARE @state_old char(2)
    DECLARE @zip_old char(15)
    DECLARE @country_old char(3)
    DECLARE @name_new char(35)
    DECLARE @adr1_new char(35)
    DECLARE @adr2_new char(35)
    DECLARE @adr3_new char(35)
    DECLARE @city_new char(30)
    DECLARE @state_new char(2)
    DECLARE @zip_new char(15)
    DECLARE @country_new char(3)
    DECLARE @user char(10)
    
    SELECT
        @cust_code = cust_code,
        @name_new = cust_name, 
        @adr1_new = cust_adr1,
        @adr2_new = cust_adr2,
        @adr3_new = cust_adr3,
        @city_new = cust_city,
        @state_new = cust_state,
        @zip_new = cust_zip,
        @country_new = cust_country,
        @user = user_code
    FROM INSERTED
    
    INSERT INTO Triggered_customer 
        (cust_code, cust_name_before, cust_adr1_before, cust_adr2_before, cust_adr3_before,
        cust_city_before, cust_state_before, cust_zip_before, cust_country_before,
        cust_name_after, cust_adr1_after, cust_adr2_after, cust_adr3_after,
        cust_city_after, cust_state_after, cust_zip_after, cust_country_after,
        user_code, TIMESTAMP_DTM, TRIGGER_OPERATION_CD)
    SELECT
        cust_code, null, null, null, null, null, null,
        null, null, cust_name, cust_adr1, cust_adr2, cust_adr3, cust_city,
        cust_state, cust_zip, cust_country, user_code, GetDate(), 'I'
FROM inserted

END

what is the deal with declaring this

DECLARE @name_old char(35)
DECLARE @adr1_old char(35)
DECLARE @adr2_old char(35)
DECLARE @adr3_old char(35)
DECLARE @city_old char(30)
DECLARE @state_old char(2)
DECLARE @zip_old char(15)
DECLARE @country_old char(3)

but never using it
you want thos then you need to join inserted on deleted pseudo table

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[FatalExceptionError]

what is the deal with declaring this

DECLARE @name_old char(35)
DECLARE @adr1_old char(35)
DECLARE @adr2_old char(35)
DECLARE @adr3_old char(35)
DECLARE @city_old char(30)
DECLARE @state_old char(2)
DECLARE @zip_old char(15)
DECLARE @country_old char(3)

but never using it
you want thos then you need to join inserted on deleted pseudo table

Not sure but I was asking the same question. I didn't write the triggers. I was supposed to test reports made that required the triggers in the first place. My test plan involved making new customers, updating etc. This issue got in the way of that.

I am slightly confused about what was wrong. Was it declaring those variables and using them in the insert despite them not being set equal to a value?

=======================================
The statement below is true
The statement above is false

There are 10 kinds of people, those who know binary and those who don't

 

[FatalExceptionError]

BTW thanks Denis

=======================================
The statement below is true
The statement above is false

There are 10 kinds of people, those who know binary and those who don't

 

[SQLDenis]

No, your problem is this

SELECT
@cust_code = cust_code,
@name_new = cust_name,
@adr1_new = cust_adr1,
@adr2_new = cust_adr2,
@adr3_new = cust_adr3,
@city_new = cust_city,
@state_new = cust_state,
@zip_new = cust_zip,
@country_new = cust_country,
@user = user_code
FROM INSERTED


the minute you have a batch that affect more than 1 row that will cause an abend (yes abend...very cobolish)

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[FatalExceptionError]

After some sleep I see.

So in this scenario isn't it pointless to even declare those variables or am I reading that wrong? If I add an if statement to an update trigger that checks if any data was actually changed(not updated), would it do this check for each update or would I have to do something special with that if.

=======================================
The statement below is true
The statement above is false

There are 10 kinds of people, those who know binary and those who don't

 

[SQLSister]

It would do the check for each updated batch of records not each record individually. Never even consider processing records individually in a trigger.

Questions about posting. See faq183-874