datafarmer
asked on
Sql trigger error - SQL 2000
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,Si te,DteUpd) Values(@COID,3,@Site,@dteu pd)
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
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,Si
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
ASKER
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,Si te,DteUpd) Values(@COID,3,@Site,@dteu pd)
End
~~~~~~~~~~~
So to be clear, when I try to update dtMavis.. exact error message is "ODBC Sql Server Driver] Invalid object name 'updated'
~~~~~~~~~~~~
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,Si
End
~~~~~~~~~~~
So to be clear, when I try to update dtMavis.. exact error message is "ODBC Sql Server Driver] Invalid object name 'updated'
ASKER
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.
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.
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
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
ASKER
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,Si te,DteUpd) Values(@COID,3,@Site,@dteu pd)
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.
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,Si
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.
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.
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.
ASKER
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,Si te,DteUpd) Values(@COID,3,@Site,@dteu pd)
End
~~~~~
Apologize for being dense... but I guess I need a slap to see what I am missing.
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,Si
End
~~~~~
Apologize for being dense... but I guess I need a slap to see what I am missing.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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:
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.
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
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.
ASKER
Flipping awesome... yes this works..... Thank you so much!
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.