Can not access the Deleted System Table

I have a problem where I can not perform "Select * from Deleted" within
a trigger, but the problem occurs when I insert/update data from
Enterprise Manager ONLY. The trigger operates fine if I modify data in
Query Analyzer or through VBCode.
I finally found a workaround, modifying the select statement to "Select
* Into #deleted from Deleted", but am worried that will impact
performance on larger databases. I'm 99% certain it is just an access
issue somewhere, I just can't figure out where.
Any help y'all can suggest is appreciated.
Here is a snippet of the trigger:
CREATE trigger InsertUpdate_Table1
On Table1
For Update, Insert As
Declare @.Temp Int
Select * from Deleted
Select @.Temp = @.@.Rowcount
If @.Temp > 0
{rest of the code has been confirmed to work just fine}> Select * from Deleted
> Select @.Temp = @.@.Rowcount
So your trigger returns 2 resultsets? I can see how this would confuse EM
because special application code is needed to handle this situation.
This technique to get the number of updated rows is a bad practice for a
number of reasons. The proper way to get the number of updated rows is with
@.@.ROWCOUNT after the UPDATE statement rather in the trigger itself:
UPDATE dbo.Table1
SET MyColumn = 1
SELECT @.@.ROWCOUNT
Hope this helps.
Dan Guzman
SQL Server MVP
"Fayven" <FayvenWren@.gmail.com> wrote in message
news:1154833842.671883.224220@.b28g2000cwb.googlegroups.com...
>I have a problem where I can not perform "Select * from Deleted" within
> a trigger, but the problem occurs when I insert/update data from
> Enterprise Manager ONLY. The trigger operates fine if I modify data in
> Query Analyzer or through VBCode.
> I finally found a workaround, modifying the select statement to "Select
> * Into #deleted from Deleted", but am worried that will impact
> performance on larger databases. I'm 99% certain it is just an access
> issue somewhere, I just can't figure out where.
> Any help y'all can suggest is appreciated.
> Here is a snippet of the trigger:
> CREATE trigger InsertUpdate_Table1
> On Table1
> For Update, Insert As
> Declare @.Temp Int
> Select * from Deleted
> Select @.Temp = @.@.Rowcount
> If @.Temp > 0
> {rest of the code has been confirmed to work just fine}
>|||FYI - SQL EM is using RPC to run the SQL Statements
--
THANKS & PLEASE RATE THE POSTING.
--RAVI--
"Fayven" wrote:

> I have a problem where I can not perform "Select * from Deleted" within
> a trigger, but the problem occurs when I insert/update data from
> Enterprise Manager ONLY. The trigger operates fine if I modify data in
> Query Analyzer or through VBCode.
> I finally found a workaround, modifying the select statement to "Select
> * Into #deleted from Deleted", but am worried that will impact
> performance on larger databases. I'm 99% certain it is just an access
> issue somewhere, I just can't figure out where.
> Any help y'all can suggest is appreciated.
> Here is a snippet of the trigger:
> CREATE trigger InsertUpdate_Table1
> On Table1
> For Update, Insert As
> Declare @.Temp Int
> Select * from Deleted
> Select @.Temp = @.@.Rowcount
> If @.Temp > 0
> {rest of the code has been confirmed to work just fine}
>sql

Can MSDE trigger call a .NET remote object?

Hi,
I wonder if MSDE trigger can call a remote .NET object
,i want to get notify when a new row was added or changed
also if I have an access application(forms etc...) can I still use it with
MSDE?
thanks in advance.
hi Julia,
"Julia" <codewizard@.012.net.il> ha scritto nel messaggio
news:u6ih4$XwEHA.4048@.TK2MSFTNGP15.phx.gbl
> Hi,
> I wonder if MSDE trigger can call a remote .NET object
> ,i want to get notify when a new row was added or changed
>
actually it should not be the case... you could perhaps "shell" out to
server's local resources, but this is not a good practice :D
this is usually not the correct scenario for client server applications...
if you really need something like that, schedule a data refresh from time to
time, say, every hour :D
stop kidding, ok.. perhaps you should rely on other forms of data
investigation..

> also if I have an access application(forms etc...) can I still use it
> with MSDE?
>
usually yes... an .ADP project will manage you connection(s) to a SQL
Server/MSDE database, while the Access project only includes forms
definitions, reports and so on..
http://www.amazon.com/exec/obidos/tg...glance&s=books
... a little dated, but still valuable book by Mary Chipman
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Can I use table data type in a trigger?

This little batch:
========================================
====
create trigger x on T after insert, update
as
declare @.z table (id int not null)
select
@.z = (select col from table)
go
========================================
====
seems to contain some invalid logic.
QA returns me that I have to declare @.z. But i do this.
Any help appreciated.
Regards.
fabri
Sei un italiano medio, di oltre trent'anni se:
Sai che il codice SYS64738 serviva per riavviare il Commodore 64 ed hai
cambiato almeno una mezza dozzina di joystick Quickshot I giocando a
Summer Games.
MKDS nick & friend Code:
Joker - 055895 043343I got it:

> create trigger x on T after insert, update
> as
> declare @.z table (id int not null)
> insert into @.z = select col from table
> go
fabri
Sei un italiano medio, di oltre trent'anni se:
Sai che il codice SYS64738 serviva per riavviare il Commodore 64 ed hai
cambiato almeno una mezza dozzina di joystick Quickshot I giocando a
Summer Games.
MKDS nick & friend Code:
Joker - 055895 043343

Can I use profiler to watch a table from an application?

I need to create an application which watches for changes to one or two
tables in a SQL Server database.
I originally considered adding a trigger to the table which would add a row
to another table recording some information about the change. This second
table would then be scanned by the application (it would delete rows it had
processed - in fact it would be a queue).
However I was wondering if I could instead create a trace which the
application could monitor in real time. It seems that traces can be created
which are written to a file, but you have to stop the trace to look at the
file.
Any suggestions?
Are you watching for changes to the table or to the data? If you are
watching for data changes why not just add a column (or two or three) along
the lines of
Created datetime
Updated datetime
Processed datetime
When data is insert or updated the corresponding column can be set with the
current date. The application that would monitor the table could use these
columns along with the Processed column to determine if it needs to do
anything.
Keith
"Graham Morris" <Graywing@.newsgroup.nospam> wrote in message
news:%234sjY0yzFHA.612@.TK2MSFTNGP10.phx.gbl...
>I need to create an application which watches for changes to one or two
>tables in a SQL Server database.
> I originally considered adding a trigger to the table which would add a
> row to another table recording some information about the change. This
> second table would then be scanned by the application (it would delete
> rows it had processed - in fact it would be a queue).
> However I was wondering if I could instead create a trace which the
> application could monitor in real time. It seems that traces can be
> created which are written to a file, but you have to stop the trace to
> look at the file.
> Any suggestions?
>
|||I want to detect changes to the data. I am trying to minimise the impact on
the database schema - creating new columns in the table is completely out
I'm afraid, but I can get away with adding triggers.
I was hoping that if I can tap in to a profiler trace, I can watch for
changes to the table. It seems though that I can't do this programmatically
the way the profiler interface does.
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:e1ACFJzzFHA.1252@.TK2MSFTNGP09.phx.gbl...
> Are you watching for changes to the table or to the data? If you are
> watching for data changes why not just add a column (or two or three)
> along the lines of
> Created datetime
> Updated datetime
> Processed datetime
> When data is insert or updated the corresponding column can be set with
> the current date. The application that would monitor the table could use
> these columns along with the Processed column to determine if it needs to
> do anything.
> --
> Keith
>
> "Graham Morris" <Graywing@.newsgroup.nospam> wrote in message
> news:%234sjY0yzFHA.612@.TK2MSFTNGP10.phx.gbl...
>
|||How do your applications insert and update data?
If they insert/update multiple rows within one statement you will have to
code your trigger in such a way as to handle that.
If the applications insert and update data via stored procedures you could
just add a second insert (or update) statement to the stored procedure so
that the appropriate data would be added to your log table.
I would stay away from the profiler trace idea. This is more for monitoring
than for firing off events.
Keith
"Graham Morris" <Graywing@.newsgroup.nospam> wrote in message
news:%23tjrpF0zFHA.3720@.TK2MSFTNGP14.phx.gbl...
>I want to detect changes to the data. I am trying to minimise the impact
>on the database schema - creating new columns in the table is completely
>out I'm afraid, but I can get away with adding triggers.
> I was hoping that if I can tap in to a profiler trace, I can watch for
> changes to the table. It seems though that I can't do this
> programmatically the way the profiler interface does.
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:e1ACFJzzFHA.1252@.TK2MSFTNGP09.phx.gbl...
>
|||OK, I'll forget the profiler idea
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:OUznc70zFHA.3408@.TK2MSFTNGP09.phx.gbl...
> How do your applications insert and update data?
> If they insert/update multiple rows within one statement you will have to
> code your trigger in such a way as to handle that.
> If the applications insert and update data via stored procedures you could
> just add a second insert (or update) statement to the stored procedure so
> that the appropriate data would be added to your log table.
> I would stay away from the profiler trace idea. This is more for
> monitoring than for firing off events.
> --
> Keith
>
> "Graham Morris" <Graywing@.newsgroup.nospam> wrote in message
> news:%23tjrpF0zFHA.3720@.TK2MSFTNGP14.phx.gbl...
>