Trigger on a view like oracle

28/05/2008 - 21:45 von Carla | Report spam
I have a logtable. In these logtable one sentence is written, before datas
are stored in some other table, after finishing work the msgstatus will be
set to new.
After these procedure a stored procedure should start to proof and
distribute the imported datas.
I realized this, with an for update-trigger if Status = new.

The other guide who has to realize the first step, has a problem when my
trigger starts, because of his transaction is not finished at that moment.

So he ask me, wether it should be possible that I do in SQL-Server something
like that.
Generate a view on the logtable.
Create an trigger on the view and do so the further actions.

But how does it work, to make an trigger on a view in SQL-Server?

Is there anybody how can help?
Thanks
 

Lesen sie die antworten

#1 Christoph Muthmann
29/05/2008 - 08:16 | Warnen spam
Carla wrote:
I have a logtable. In these logtable one sentence is written, before
datas are stored in some other table, after finishing work the
msgstatus will be set to new.
After these procedure a stored procedure should start to proof and
distribute the imported datas.
I realized this, with an for update-trigger if Status = new.

The other guide who has to realize the first step, has a problem when
my trigger starts, because of his transaction is not finished at that
moment.

So he ask me, wether it should be possible that I do in SQL-Server
something like that.
Generate a view on the logtable.
Create an trigger on the view and do so the further actions.

But how does it work, to make an trigger on a view in SQL-Server?

Is there anybody how can help?



Hi Carla,
this is a german newsgroup, so I will reply in german. If this is a problem
for you, please let me know.

Ich würde den Prozess anders gestalten. Wie ihr bereits festgestellt habt,
sollten es zwei unabhàngige Schritte sein, die ihr aber über einen Trigger
fest miteinander verheiratet habt.

Möglichkeit a (asynchron):
Es gibt einen Job, der (z. B.) alle 2 Minuten nachschaut, ob es Sàtze mit
NEW gibt und diese verarbeitet.

Möglichkeit b (synchron):
Aus der ersten Prozedur heraus wird die zweite Prozedur direkt gestartet,
nachdem das Commit durch ist.

Möglichkeit c (synchronisiert)
Aus der ersten Prozedur heraus wird ein Job gestartet, der die zweite
Prozedur aufruft, nachdem das Commit durch ist. Die erste Prozedur wartet
nicht auf das Ende des Jobs.


PS: Was sollte ein Trigger auf einer View anders machen, als wenn er direkt
auf der Tabelle liegt?

Einen schönen Tag noch,
Christoph
Microsoft SQL Server MVP
http://www.insidesql.org
(Please post ALL replies to the newsgroup only unless indicated
otherwise)

Ähnliche fragen