asked on

I have an UPDATE trigger in SQL Server 2008 which constantly disappears

Hi,

I have the same application running on 2x platforms. Both systems have a dedicated DB server, one runs SQL Server 2005, the other 2008 (R2)

On both dbs I have a table and on that table there should be 2x triggers, one managing inserts, the other updates.

On the 2005 server, I have no issues.

On the 2008 server, the insert trigger is fine but the update trigger keeps disappearing / vanishing without any explanation. Literally, I create the trigger from a stored script, refresh the triggers branch and I can see it. I'll come back 10-15 mins later and refresh again - I can see the insert trigger but the update trigger has gone. I haven't dropped the trigger nor the table nor do I believe anyone else could or would be taking actions which conflicted with my work (e.g. colleagues working on the same db instance).

The triggers aren't complex - script is attached.

Confused :-(

Thanks in advance. Changes-to-IssueHeader-Triggers-.sql

Guy Hengel [angelIII / a3]

either someone drops/recreates the table, or indeed drops the trigger.

check out this to "see/trace" the event: ddl triggers
http://www.sql-server-performance.com/2007/ddl-triggers/

now, if that trigger also disappears, then you have someone that either really drops all triggers in the database, or even fully "restores" the db.
if it's a database restore, you can see that in the sql server ERRORLOG file
otherwise, use the SQL Profiler tool to trace all sql, and find the culprit there.

ASKER CERTIFIED SOLUTION

LogistixSW

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

LogistixSW

ASKER

My own dumbass fault :(

Lowfatspread

ok glad you found that ...

however ,,,, of longer term importance...

please write your triggers correctly ...

1) to handle multiple row situations
2) to just reference the changed set of data..

e.g. write it more like

create trigger [dbo].[TG_IssueHeader_INSERT] on [dbo].[IssueHeader]
for insert
as
begin
            update IssueHeader
            set 
                  IssueHdr_WhseRef = cast(I.IssueHdr_ID as varchar(20))
            from issueheader as h
            inner join Inserted as I
              on h.IssueHdr_ID =i.IssueHdr_ID 

            where len(coalesce(h.issuehdt_whseref,'')<1
    
end

Open in new window