Solved

Sql trigger error - SQL 2000

Posted on 2014-09-10
10
201 Views
Last Modified: 2014-09-16
Not that familiar with triggers...... we already have one set up for this table but here is the error we get:       updated is not an object      - when an update to the table occurs.
Here  is the trigger info:
~~~~~~~~~~~~~~~~~~~~~~
CREATE TRIGGER  Davis_Upd_TblNotify  ON [dbo].[dtMavis]
     FOR UPDATE

AS
BEGIN
   declare @COID varchar(5);
   declare @Site varchar(2);
   declare @Tmp varchar(8);
   declare @dteupd  datetime;
   Set @dteupd = getdate();

   Select @COID=Inserted.COID  From  updated i;
  Set @Site =(Select SubString(WhoUpd,6,2) From Updated)

       Update DbTblNotify  Set DteUpd=getDate() Where COID=@COID AND TranID=3 And Site=@Site

       If @@ROWCOUNT=0
          Insert Into DbTblNotify(COID,TranID,Site,DteUpd) Values(@COID,3,@Site,@dteupd)
      End
~~~~~~~~~~~

The error we get says Updates is not an object...... I have tried numerous variations of the above... but still get same error. {have tried it with the "i", without the "i", ...)

What am I doing wrong (this is for Sql 2000
0
Comment
Question by:datafarmer
  • 5
  • 4
10 Comments
 
LVL 28

Expert Comment

by:sammySeltzer
ID: 40315735
I don't think trigger has UPDATED.

It is either selected from inserted or deleted but not updated

I believe you want to select from Inserted.

Or if you wish to update based on updated record, then use After Update trigger.
0
 

Author Comment

by:datafarmer
ID: 40315753
I tried that as well....
~~~~~~~~~~~~
CREATE TRIGGER Davis_Upd_TblNotify  ON [dbo].[dtMavis]      AFTER UPDATE
AS
BEGIN
   declare @COID varchar(5);
   declare @Site varchar(2);
   declare @Tmp varchar(8);
   declare @dteupd  datetime;
   Set @dteupd = getdate();

   Select @COID=Inserted.COID  From  updated i;
  Set @Site =(Select SubString(WhoUpd,6,2) From Updated )

       Update DbTblNotify  Set DteUpd=getDate() Where COID=@COID AND TranID=3 And Site=@Site

       If @@ROWCOUNT=0
          Insert Into DbTblNotify(COID,TranID,Site,DteUpd) Values(@COID,3,@Site,@dteupd)
      End
~~~~~~~~~~~
So to be clear, when I try to update dtMavis.. exact error message is "ODBC Sql Server Driver] Invalid object name 'updated'
0
 

Author Comment

by:datafarmer
ID: 40315766
Let me add to the comment...
So if  I use:  
 CREATE TRIGGER Davis_Upd_TblNotify  ON [dbo].[dtMavis]      AFTER UPDATE
AS
BEGIN
   declare @COID varchar(5);
   declare @Site varchar(2);
   declare @Tmp varchar(8);
   declare @dteupd  datetime;
   Set @dteupd = getdate();

   Select @COID=(Select COID  From  updated)
  Set @Site =(Select SubString(WhoUpd,6,2) From Updated )

** That works as far as the trigger actually firing with out an error... but then I get a blank COID, which let me to believe that I was still using the incorrect syntax - because I am not getting a COID.
True, the COID itself, is not being updated.. but I would expect to get the COID back.
0
 
LVL 28

Expert Comment

by:sammySeltzer
ID: 40315768
You can't use:

 Select @COID=Inserted.COID  From  updated i;

You have to change it to:

 Select @COID=Inserted.COID  From  inserted i;

However, depending on what you are trying to do, you can try to find out if a table is updated with a value and then update it using this link:

http://msdn.microsoft.com/en-us/library/ms187326.aspx
0
 

Author Comment

by:datafarmer
ID: 40315790
Let me be show the updated trigger:
CREATE TRIGGER Davis_Upd_TblNotify  ON [dbo].[dtMavis]      AFTER UPDATE
AS
BEGIN

    declare @COID varchar(5);
   declare @Site varchar(2);
   declare @Tmp varchar(8);
   declare @dteupd  datetime;
   Set @dteupd = getdate();

   
  Set @COID =(Select COID From inserted i)
  Set @Site =(Select SubString(WhoUpd,6,2) From inserted i)

      
       Update DbTblNotify  Set DteUpd=getDate(), EdtSq=EdtSq+1 Where COID=@COID AND TranID=3 And Site=@Site

       If @@ROWCOUNT=0
          Insert Into DbTblNotify(COID,TranID,Site,DteUpd) Values(@COID,3,@Site,@dteupd)
      End
~~~
The above does work.... but I am not getting the COID back, even though there is a COID in the record.
What happens then, is I try to add the COID, TraniD, Site... and get a duplicate key error because a blank @COID has already been added (along with a blank @site field

All that I am trying to do is update the DbTblNotify with the COID (from the Record being updated) and the SiteID.   Both seem to values seem to be blank, even though there are valid values in record.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 28

Expert Comment

by:sammySeltzer
ID: 40315821
Ok let's start with this:

 Set @COID =(Select COID From inserted i)
   Set @Site =(Select SubString(WhoUpd,6,2) From inserted i)

what are you trying to accomplish here?

The code tells me that when a record is inserted grad the COID and whoUpd of that inserted record.

Is that what you really want to do?

I don't think your code reflects that.

Also, you can streamline code above into just one line:

 SELECT @COID =COID,  @Site = SubString(WhoUpd,6,2) From inserted i)

Finally, you will need to test your insert and update by hardcoding the values.

If they work, then you know where the problem lies.

Take a quick look at the link I provided to see how to check for an updated record before performing any action.
0
 

Author Comment

by:datafarmer
ID: 40315851
This exactly what I am trying to accomplish:
If this query runs:
Update dtMavis  Set Cat='AC',Dteupd=getdate() where COID='FSL' AND Recnum='Aaa000003'

I want the DbTblNotify  (table) to be updated with the COID (in this case FSL), the current date/time and
the site ID that would get from the WhoUpd field.    

This is what I coded...
CREATE TRIGGER  Mavis_Upd_TblNotify  ON dtMavis     AFTER UPDATE
AS
BEGIN

    declare @COID varchar(5);
   declare @Site varchar(2);
   declare @dteupd  datetime;
   Set @dteupd = getdate();

   
       Select @COID = COID, @Site = SubString(WhoUpd,6,2) From updated
---  What I still don't understand is...  I would think that since I relying on a row to be updated,
---  and not inserted.... I would use updated.  But when I do, I get invalid object  = updated.  
---  When I use inserted... I get null values on COID and site ID... both of which have values.
---- I did read the article, thank you... but I guess I since I don't care which columns are updated,
 --- as long as the record is being updated... what I am in missing.

      
       Update DbTblNotify  Set DteUpd=getDate(), EdtSq=EdtSq+1  Where COID=@COID AND TranID=3 And Site=@Site

       If @@ROWCOUNT=0
          Insert Into DbTblNotify(COID,TranID,Site,DteUpd) Values(@COID,3,@Site,@dteupd)
      End
~~~~~
Apologize for being dense... but I guess I need a slap to see what I am missing.
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
ID: 40315899
You need to do this set-based rather than assuming only one row is being UPDATEd.  SQL Server only calls a trigger once per UPDATE/statement, no matter how many rows are affected.


CREATE TRIGGER Davis_Upd_TblNotify
  ON [dbo].[dtMavis]
  AFTER UPDATE
AS
SET NOCOUNT ON;

UPDATE dtn
SET
    DteUpd = GETDATE(),
    EdtSq = EdtSq+1
FROM inserted i
INNER JOIN DbTblNotify dtn ON
    dtn.COID = i.COID AND
    dtn.Site = SUBSTRING(i.WhoUpd, 6, 2) AND
    dtn.TranID = 3

INSERT INTO DbTblNotify ( COID, TranID, Site, EdtSq, DteUpd )
SELECT
    i.COID, 3 AS TranID,
    SUBSTRING(i.WhoUpd, 6, 2) AS Site,
    1 AS EdtSq, GETDATE() AS DteUpd
FROM inserted i
LEFT OUTER JOIN DbTblNotify dtn ON
    dtn.COID = i.COID AND
    dtn.Site = SUBSTRING(i.WhoUpd, 6, 2) AND
    dtn.TranID = 3
WHERE
    dtn.COID IS NULL

GO
0
 
LVL 28

Expert Comment

by:sammySeltzer
ID: 40315906
You use a trigger because you want certain operation to occur when triggered by something.

In your case it seems, when a table is updated with WhoUpd field, you want to perform an update or insert.

Something like this:

CREATE TRIGGER  Mavis_Upd_TblNotify  ON 
dtMavis    
 AFTER UPDATE
AS
IF (UPDATE WhoUpd )
BEGIN
Update DbTblNotify  d 
Set DteUpd=getDate(), EdtSq=EdtSq+1 
FROM theOtherTable t
INNER JOIN t ON d.TranID= t.TranID
Where d.TranID=3 
END
GO

Open in new window


Try this and see what happens.

You may have to make changes based on your understanding of your domain and data structure.

If this works, then you can work in the INSERT bit.
0
 

Author Closing Comment

by:datafarmer
ID: 40326891
Flipping awesome... yes this works.....   Thank you so much!
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now