Trigger error

I have a delete trigger that is trying to get information during the delete 
using the deleted object and I am getting the errors below.

The multi-part identifier "deleted.RepCompanyID" could not be bound.
The multi-part identifier "deleted.BrandID" could not be bound.

The trigger code giving the error is below.
p.s. This is from an old database so alternatives in constraint use would be 
ok.  Thanks.

    DECLARE @RepCompany varchar(200);
    DECLARE @Brand nvarchar(50);
    DECLARE @OldInfo varchar(200);
    SET @RepCompany = (SELECT RepCompany FROM dbo.RepCompany WHERE 
RepCompanyID = deleted.RepCompanyID);
    SET @Brand = (SELECT Brand FROM dbo.Brands WHERE BrandID = 
deleted.BrandID);
    SET @OldInfo = @RepCompany + '-Brand=' + @Brand;


-- 
David
0
Utf
6/16/2010 10:18:28 PM
sqlserver.programming 1873 articles. 0 followers. Follow

1 Replies
425 Views

Similar Articles

[PageSpeed] 8

This is what you're looking for:

SELECT
@RepCompany = RepCompany.RepCompany
@Brand = Brands.Brand
FROM deleted
LEFT JOIN dbo.RepCompany ON RepCompany.RepCompanyID =
deleted.RepCompanyID
LEFT JOIN dbo.Brands ON Brands.BrandID = deleted.BrandID

SET @OldInfo = @RepCompany + '-Brand=' + @Brand;

But what you'll find is that it only works when one row is deleted at
a time.  If Delete * from RepCompany is executed, or Delete * from
RepCompany where BrandID = 123 is executed then only one row will be
tracked by this delete trigger.  You should be doing it in sets using
the delete table instead of one row at a time from the delete table.

I hope that helps.

-Eric Isaacs
0
Eric
6/16/2010 11:49:16 PM
Reply:

Similar Artilces:

Modifier
I'm getting the following script error: Unhandled script exception: Illegal address for field '[Not Found]' in script 'SaveRecord'. Script terminated. EXCEPTION_CLASS_SCRIPT_ADDRESSING SCRIPT_CMD_LOAD The reference in the error message to 'SaveRecord' doesn't make sense to me, as we have no script or code by that name. The VBA code that runs (and executes completely) just before this error is raised is the following: Private Sub Save_BeforeUserChanged(KeepFocus As Boolean, CancelLogic As Boolean) On Error GoTo ErrInSub If KeyFieldsNonEmpty Then ...

VAL Function Error
I have been using the VAL function for a couple years with no problem in Access 2003, but have recently come across a particular sequence of letter and numbers that causes an error, which I don't understand. Based on the following input and output: Original Value Val Result 6240b10043105 6240 6240c10043105 6240 6240D10043105 #Error 6240e10043105 #Error 6240f10043105 6240 6240g10043105 6240 6240d10 6.24E+13 6240d100 6.24E+103 6240d1001 #Error Why do the original values with 'e' or 'd' in the 5th character error if the le...

Error messages #12
I get the following error message when trying to download my email in Outlook 2002: "0x80042108 Outlook is unable to connect to your incoming server" Any suggestions? If you use the Test Account Settings, do you get an error? Is it the same one? If yes, then try contacting your ISP to see if they are having problems. -- Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without reading. After furious head-scratching, Richard ask...

Error 3045 on an MDE file
Hi I hope you can help me with this it is driving me nuts. I've created a database which I needed to give multiple people access to but didn't want them messing so I created an MDE file. I ensured that the MDB file was set to shared, not compacting and set to run users not owners permissions before I converted. However, if one of the users goes into the database, it does not create and LDB file and if anyone else tries to get it, the error message: Could not use <name>; file already in use. (Error 3045) appears. Some of the tables in the MDE files were linking to and MDB fi...

Workflow paused without errors
Hi, all of my workflows are not executed. Workflow monitor displays them as paused, but the log doesn't contain any information what's wrong. The event log contains an error message for each workflow and reffers to the workflow monitor where no additional information can be found. I have also experienced this behaviour with past beta versions - maybe I am missing an important step in the process of designing workflows? Any help is highly appreciated! Thank you. Regards Andreas I dont think that you have provided enough info to troubleshoot this. If you can export one of th...

Money 2004 illegal operation error
Hi, Recently, about a week ago, whenever I close my Money 2004 program I get an illegal operation error as follows; MSN has caused an invalid page fault in Kernel32.dll at 0167:bff9db61. I have checked everywhere I can think of to find the problem with no results. This only occurs when I close the program. Sometimes when I click close on the error message it goes away and I can continue normally, other times the error message just keeps popping right back and I have to reboot to get rid of it. I have tried uninstalling and reinstalling with no luck. I am using Windows 98se and IE 6.0. T...

Rob...Cost update trigger
I found a post that originally included a cost update trigger that Rob had created. It updates the sales price based on a new cost. I can't get the attachment anymore. Anybody out there have the trigger? Thanks, G E ...

Trigger email
Is there a way that you can have a countdown and when the countdown reaches 0 for it to automaticly send out a email to a certian person? You will first have to determine how you are going to calculate your countdown. Then try the following: Dim strSubject As String Dim strAddress As String Dim strMessage As String Dim blSendEmail as Boolean strSubject = "Countdown reached zero" strMessage = "This is the message that will be the body of the email." If 'This is where you would put the countdown check value' Then ...

Hibernating error message
I've get this message when turning on Microsoft word on my home laptop. How can I clear it What message? On May 27, 4:54=A0pm, MoeMrtn <MoeM...@discussions.microsoft.com> wrote: > I've get this message when turning on Microsoft word on my home laptop. = =A0How > can I clear it ...

Error when tracking in CRM
Hi, When tracking (in CRM) an email in outlook the folowing error occurs: An error occured promoting this item to Microsoft CRM. The Microsoft CRM server could not be contacted or the user has insufficient permission to perform this action. Do you want to send this e-mail? If you click Yes, the e-mail will be sent out, but no corresponding activity will be created in Microsoft CRM. Any ideas? Thanks in advance, Chris ...

MSN Money Historical Charts are in Error
Can someone at Microsoft fix the Historical Charts so they are correct. Just to take one specific example, if I look at the 10-year growth chart for VCAIX (any stock will do) at http://moneycentral.msn.com/investor/partsub/funds/returns.asp?Funds=1&Symbol=US%3aVCAIX it tells me the 10 year growth of $10000 to the present time (8/08) is approximately +17%. However, if I click right under neath that graph on the link saying "view a more detailed chart", it displays the same chart that I get directly from MSN Portfolio Tracker showing a LOSS of ~20% FOR THE VERY SAME STOCK. ...

Outlook Error #45
Dear all, Pls help me, after i install siemens hotsync my outlook error. "The add in "siemens quicksyncbutton.dll couldn't be installed or loaded......" What must i do ? I already reinstall the MS Office but the error still appear. Thank's for your help ...

Error with Chart as new sheet
Hi there. I have a strange problem whereby I cannot move a newly created chart to a new sheet. I am running Microsoft Office 2007 Professional edition. What happens is, when I create a new chart, the chart comes up in the current worksheet. When I try the move the chart (either by hitting the move chart button on the ribbon, or by right clicking on the chart and selecting move chart) the chart disappears from the current worksheet and a new worksheet (chartx) opens but is completely blank (no white background, nothing, just a blue background like the chart is minimised or something - ...

Visio Error
I was working in Visio on a document and I accidently hit my power switch (the one on the floor on the cord) and my PC turned off. When my machine rebooted and I tried to access the same Visio file, a message tells me that I cannot access the file because it is NOT a Visio file???? I tried renaming it, didn't help. This is the second time this happened to me and I can't understand why the program is not recognizing the file. It's very frustrating....does anyone have any answers to this? - Sally Another option is to turn on "Autorecovery" It will automatic...

Error in Access 2007
I created an application some time ago using Access 2000. It worked fine for a long time. Now I have Access 2007 and Access 2000 installed on my machine with Win Vista. Now when I try to open the Access Application in either version I get the following error: "The expression on open you entered as the event property setting produced the following error: Object or class does not support the set of events" I am stymied. Please help! John King Could it be this issue: http://allenbrowne.com/bug-17.html -- Allen Browne - Microsoft MVP. Perth, Western Austr...

Metabase Cache Error
Okay... I'm new to this. I have MSCRM setup and running well through web access, however, in the Outlook client I get an error when I try to view the CRM records Offline. I can view them just fine throught the CRM website, and also through the Outlook client, when I'm online. But when I sync and go offline, I get the following error: Metabase Cache Error (then in the window below) No Entity (with a big red "X") What am I doing wrong? Thanks! ...

Error on start
Excel 2003 is producing the following error on start-up: --------------------------- NET Co Library --------------------------- System.Runtime.InteropServices.COMException (0x800A03EC): Exception from HRESULT: 0x800A03EC at Excel._Application.get_VBE() at NETCoLibrary.AddinModule.Add_Menu_Items() --------------------------- OK --------------------------- I have two add-ins installed (MZ Tools and Smart Indenter). Both work.There are no other add-ins installed (either in VBA or Excel). This does not occur every time I start Excel. I cannot recognise a pattern in my use o...

PM_Key_MSTR Table Error
I am trying to post my batches in Cash Book Management. But I get the error " Value exists in the PM_Key_MSTR table" I went to check the table, PM00400 but did not find the the check numbers am trying to post there and neither are there duplicate values/records found. Please what is wrong? -- pibsng Its the first two fields that must be unique, the CNTRLNUM and CNTRLTYP combined. Run a checklinks and reconcile on this table. -- Richard L. Whaley Author / Consultant / MVP 2006-2008 Documentation for Software Users Get our Free Tips and Tricks Newsletter and check out our b...

error during loading crystal report in CRM
Hi, having created a new report in the crystal report designer, I get an error when the report is included in the Microsoft CRM. The error has the following content: "Fehler beim Laden der Datenbankinformationen" or "Error during loading databaseinformations" I have connected to the database with an OLE DB ADO connection. In the designer this works fine but in the Microsoft CRM system the error occurs. I belive the way of connecting is the problem and a possible solution could be to use the so called microsoft CRM connector. My question is: Is this the right idea and whe...

Event 12002 Errors
We are seeing the following error logged every couple of seconds for a user here. His mailbox has been removed (deleted and purged) and we are STILL seeing this error. Does anyone have any ideas what would cause this or how to stop these errors? Error 8004011B-820003A2 occurred while processing message <> from 'David Morris'. Thanks in advance, Jim FYI HOTFIX 829093 fixed this problem for us. http://support.microsoft.com/default.aspx?scid=kb;en-us;829093 Mention that this is a free hotfix, when you contact Microsoft Support Services (800) 936-4900 for the hotfix. It requir...

Trigger
CREATE TABLE tblA ( Symbol varchar(50) NOT NULL, APIFormat varchar(50) NULL, DataFormat varchar(50) NULL ) I would like to do the following: If a new row is added or APIFormat is edited, and DataFormat is not being updated, I would like to set DataFormat like this: If RIGHT(APIFORMAT,1) = '#', then SET DataFormat = LEFT(APIFORMAT,LEN(APIFORMAT)-1) + '0' If right(apiformat,1) <> '#', then SET DataFormat = APIFORMAT If I do the below trigger, if I don't update DataFormat, it sets it correctly, but...

Error #9
What have I done? My outlook will not open. I get an error message: OUTLOOK .exe has generated errors and will be closed by Windows. You will need to restart the program. What do I do??? Carol cogitated deeply and scribbled thusly: > What have I done? My outlook will not open. I get an > error message: OUTLOOK .exe has generated errors and will > be closed by Windows. You will need to restart the program. > What do I do??? Detect and Repair. -- gordonATgbpcomputingDOTcoDOTuk To email me replace the obvious! Gordon wrote: > Carol cogitated deeply and scribbled thusl...

Outlook reply error (associated with SP3?)
Greetings. I recently bought a new Dell, wiped the drive, performed a clean install of my own licensed copies of XP Pro and Office XP Pro, and migrated all my settings from my old computer. For a few glorious days I enjoyed newfound speediness with no glitches whatsoever. Now I am running into the following message when I reply to a message in Outlook: "A program is trying to access email addresses you have stored in Outlook. Do you want to allow this?" If I click Yes or No the new message window opens; both do the same thing. (Of course, clicking Yes means that I won't see th...

Excel Function VLOOKUP
Hi, I'm having trouble looking up a table of "Names". The table deifene below is called ROL_IS and list hundred of other defined tables. Error where: =VLOOKUP(A33,VLOOKUP(CUR_MON,ROL_IS,3,FALSE),2,FALSE) but no errors where =VLOOKUP(A33,ACT_IS,2,FALSE) or =VLOOKUP(CUR_MON,ROL_IS,3,FALSE) ANS: ACT_IS The array function in VLOOKUP appears not to converting formula to NAME. If anyone knows or needs further details if would be appreciated. Thanks Elizabet -- Message posted from http://www.ExcelForum.com If I understand you correctly =VLOOKUP(A33,INDIRECT(VLOOKU...

formula error
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel =COUNTIF(DIARY!$I$2:$I$3068,1) <br> The above formula does not automatically update to increase the range of cells as new data is being added to sheet diary. How do I correct this. Also the sheet has over 200 items. Do I have to correct the formula for each cell or is it possible to correct the range in all at one go? <br> Thanks You need to format the range as either a List or a Dynamic Range. A List is simpler. See the Excel Help topic "Create a list". For Dynamic Ranges, see here: ...