SQL2005 Delete Trigger

jdr0606
jdr0606 used Ask the Experts™
on
I have an update, insert and a delete trigger on on of my tables.

The update and insert work as expected by writing an audit record, however the delete doesn't do anything.

Attached is the delete trigger code.

RM00101 can only have a single custnmbr.

What am I missing?

Thanks
ALTER    TRIGGER [dbo].[tr_record_rm00101_delete]
ON [dbo].[RM00101]
after delete AS

      BEGIN

      INSERT dbo.bak_rm00101
    (login_Name,
    user_name,
    spid,
    hostname,
    bak_type,get_date,CUSTNMBR, CUSTNAME, CUSTCLAS, CPRCSTNM, CNTCPRSN, STMTNAME, SHRTNAME, ADRSCODE, UPSZONE,
    SHIPMTHD, TAXSCHID, ADDRESS1, ADDRESS2, ADDRESS3, COUNTRY, CITY, STATE, ZIP, PHONE1, 
    PHONE2, PHONE3, FAX, PRBTADCD, PRSTADCD, STADDRCD, SLPRSNID, CHEKBKID, PYMTRMID, CRLMTTYP,
    CRLMTAMT, CRLMTPER, CRLMTPAM, CURNCYID, RATETPID, CUSTDISC, PRCLEVEL, MINPYTYP, MINPYDLR, 
    MINPYPCT, FNCHATYP, FNCHPCNT, FINCHDLR, MXWOFTYP, MXWROFAM, COMMENT1, COMMENT2, USERDEF1, 
    USERDEF2, TAXEXMT1, TAXEXMT2, TXRGNNUM, BALNCTYP, STMTCYCL, BANKNAME, BNKBRNCH, SALSTERR, 
    DEFCACTY, RMCSHACC, RMARACC, RMSLSACC, RMIVACC, RMCOSACC, RMTAKACC, RMAVACC, RMFCGACC, 
    RMWRACC, FRSTINDT, INACTIVE, HOLD, CRCARDID, CRCRDNUM, CCRDXPDT, KPDSTHST, KPCALHST, 
    KPERHIST, KPTRXHST, NOTEINDX, CREATDDT, MODIFDT, Revalue_Customer, Post_Results_To, FINCHID, 
    GOVCRPID, GOVINDID, DISGRPER, DUEGRPER, DOCFMTID, DEX_ROW_ID )

         SELECT (system_user)login_Name,
        (user)user_name,
        (@@spid)spid,
        (host_name())hostname,
        ('Delete')bak_type,(getdate())get_date,ins.CUSTNMBR, ins.CUSTNAME, ins.CUSTCLAS, ins.CPRCSTNM, ins.CNTCPRSN, ins.STMTNAME, ins.SHRTNAME, ins.ADRSCODE, ins.UPSZONE,
        ins.SHIPMTHD, ins.TAXSCHID, ins.ADDRESS1, ins.ADDRESS2, ins.ADDRESS3, ins.COUNTRY, ins.CITY, ins.STATE, ins.ZIP, ins.PHONE1, 
        ins.PHONE2, ins.PHONE3, ins.FAX, ins.PRBTADCD, ins.PRSTADCD, ins.STADDRCD, ins.SLPRSNID, ins.CHEKBKID, ins.PYMTRMID, ins.CRLMTTYP,
        ins.CRLMTAMT, ins.CRLMTPER, ins.CRLMTPAM, ins.CURNCYID, ins.RATETPID, ins.CUSTDISC, ins.PRCLEVEL, ins.MINPYTYP, ins.MINPYDLR, 
        ins.MINPYPCT, ins.FNCHATYP, ins.FNCHPCNT, ins.FINCHDLR, ins.MXWOFTYP, ins.MXWROFAM, ins.COMMENT1, ins.COMMENT2, ins.USERDEF1, 
        ins.USERDEF2, ins.TAXEXMT1, ins.TAXEXMT2, ins.TXRGNNUM, ins.BALNCTYP, ins.STMTCYCL, ins.BANKNAME, ins.BNKBRNCH, ins.SALSTERR, 
        ins.DEFCACTY, ins.RMCSHACC, ins.RMARACC, ins.RMSLSACC, ins.RMIVACC, ins.RMCOSACC, ins.RMTAKACC, ins.RMAVACC, ins.RMFCGACC, 
        ins.RMWRACC, ins.FRSTINDT, ins.INACTIVE, ins.HOLD, ins.CRCARDID, ins.CRCRDNUM, ins.CCRDXPDT, ins.KPDSTHST, ins.KPCALHST, 
        ins.KPERHIST, ins.KPTRXHST, ins.NOTEINDX, ins.CREATDDT, ins.MODIFDT, ins.Revalue_Customer, ins.Post_Results_To, ins.FINCHID, 
        ins.GOVCRPID, ins.GOVINDID, ins.DISGRPER, ins.DUEGRPER, ins.DOCFMTID, ins.DEX_ROW_ID  

    FROM deleted ins, dbo.rm00101 a
	where ins.custnmbr = a.custnmbr  

--	FROM deleted ins left join dbo.rm00101 a on ins.custnmbr = a.custnmbr
   END

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior .Net Developer
Commented:
where ins.custnmbr = a.custnmbr  

you don't need this, you're not using anything from a.

Commented:
use left outer join

 FROM deleted ins
Left
Outer
Join    dbo.rm00101 a On ins.custnmbr = a.custnmbr

Author

Commented:
That worked!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial