[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

trigger to determine if row is inserted or updated

Magnus Blomberg

3/21/2007 4:32:00 PM

Hello!
I thought this was an easy one (and probably it is)!?!?

I have a trigger that I want to do almost the same thing if a row is updated
or inserted, but I can't figure out how to find out if a row is inserted or
updated. How do I do that?

Best regards
/Magnus


9 Answers

Russell Fields

3/21/2007 4:43:00 PM

0

Magnus,

A row that is only in the inserted table was inserted.

A row that is in both the inserted and deleted table was updated.

A row that is only in the deleted table was deleted.

RLF
"Magnus" <magnus.blomberg@skanska.se> wrote in message
news:Oqhvqa9aHHA.2552@TK2MSFTNGP06.phx.gbl...
> Hello!
> I thought this was an easy one (and probably it is)!?!?
>
> I have a trigger that I want to do almost the same thing if a row is
> updated or inserted, but I can't figure out how to find out if a row is
> inserted or updated. How do I do that?
>
> Best regards
> /Magnus
>


Tom Cooper

3/21/2007 4:50:00 PM

0

You can test this by checking if there are rows in the inserted and/or
deleted table - for example
If Exists(Select * From inserted)...

If both tables have rows, then the trigger was fired because of an UPDATE
statement.
If inserted has rows and deleted doesn't, then the trigger was fired because
of an INSERT statement.
If deleted has rows and inserted doesn't, then the trigger was fired because
of an DELETE statement.
If neither table has rows, then you can't tell what type of statement it
was, but you do know that whatever it was, it didn't do anything, for
example an UPDATE statement with a WHERE clause that didn't return true for
any row.

Tom

"Magnus" <magnus.blomberg@skanska.se> wrote in message
news:Oqhvqa9aHHA.2552@TK2MSFTNGP06.phx.gbl...
> Hello!
> I thought this was an easy one (and probably it is)!?!?
>
> I have a trigger that I want to do almost the same thing if a row is
> updated or inserted, but I can't figure out how to find out if a row is
> inserted or updated. How do I do that?
>
> Best regards
> /Magnus
>


Magnus Blomberg

3/21/2007 11:31:00 PM

0

Thank you both!

I had no clue about rows existing in the deleted "table" if it's updated. I
really don't get why. Who is behind that idea?!?!
Well, it will probably work, so then I'm happy.
Best regards /Magnus

"Tom Cooper" <tomcooper@comcast.no.spam.please.net> wrote in message
news:OgByuj9aHHA.4616@TK2MSFTNGP03.phx.gbl...
> You can test this by checking if there are rows in the inserted and/or
> deleted table - for example
> If Exists(Select * From inserted)...
>
> If both tables have rows, then the trigger was fired because of an UPDATE
> statement.
> If inserted has rows and deleted doesn't, then the trigger was fired
> because of an INSERT statement.
> If deleted has rows and inserted doesn't, then the trigger was fired
> because of an DELETE statement.
> If neither table has rows, then you can't tell what type of statement it
> was, but you do know that whatever it was, it didn't do anything, for
> example an UPDATE statement with a WHERE clause that didn't return true
> for any row.
>
> Tom
>
> "Magnus" <magnus.blomberg@skanska.se> wrote in message
> news:Oqhvqa9aHHA.2552@TK2MSFTNGP06.phx.gbl...
>> Hello!
>> I thought this was an easy one (and probably it is)!?!?
>>
>> I have a trigger that I want to do almost the same thing if a row is
>> updated or inserted, but I can't figure out how to find out if a row is
>> inserted or updated. How do I do that?
>>
>> Best regards
>> /Magnus
>>
>
>


Hugo Kornelis

3/21/2007 11:39:00 PM

0

On Thu, 22 Mar 2007 00:31:06 +0100, Magnus wrote:

>Thank you both!
>
>I had no clue about rows existing in the deleted "table" if it's updated. I
>really don't get why. Who is behind that idea?!?!

Hi Magnus,

An update is conceptually equal to a delete and an insert, executed
simultaneously.

The other reason for having rows in deleted for an update, is that some
triggers need to know the original values. They can be found there,
whereas the new values are in inserted.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hug...

Magnus Blomberg

3/26/2007 8:11:00 AM

0

Thank you!

Well. This make sence. Acctually my next question would have been about how
to resolve the original values, but you wrote it here!!

BR Magnus


"Hugo Kornelis" <hugo@perFact.REMOVETHIS.info.INVALID> wrote in message
news:vdg303d127hq9dae3t8t8ger3c0f3mi9g7@4ax.com...
> On Thu, 22 Mar 2007 00:31:06 +0100, Magnus wrote:
>
>>Thank you both!
>>
>>I had no clue about rows existing in the deleted "table" if it's updated.
>>I
>>really don't get why. Who is behind that idea?!?!
>
> Hi Magnus,
>
> An update is conceptually equal to a delete and an insert, executed
> simultaneously.
>
> The other reason for having rows in deleted for an update, is that some
> triggers need to know the original values. They can be found there,
> whereas the new values are in inserted.
>
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog: http://sqlblog.com/blogs/hug...


Barbara Schwarz

4/4/2008 1:40:00 AM

0

On Apr 3, 8:28 pm, smith.jef...@yahoo.com wrote:
> T

I assume you are on drugs.

You post unintelligently, and you are a waste of time. You are biased,
you are fanatical, you lie, and you accuse others on what you are
guilty of.

You provided no shred of evidence that you ever were in the orgs. I
post with my real name. It is broadly known that I am a Scientologist
for many years.

Your posts show that you WERE NEVER A SCIENTOLGGIST. You post about
Scientologists as they were unstable and anybody can order them and
that is false and insulting. And you are afraid to post with your real
name for one reason only: you have no connection with SCN, just as the
Butterlie has not.

You are just lonely hatemongers who have nothing else to do.

You just repeat the lies of other extremists.

Post your real name and your exact story in SCN, one that can be
verified or I treat you as what you are: A NOBODY WHO NEVER WAS IN
SCIENTOLOGY.



Barbara Schwarz

ChefXenu

4/4/2008 1:47:00 AM

0

On Apr 3, 9:28 pm, smith.jef...@yahoo.com wrote:
> Thank you butterflygirl.
>
> It funny how BS claims I am not tell the truth , YET SHE CAN NOT
> PROVIDE A SINGLE FACT TO DISPUTE WHAT I SAY.
>
> FACT: There are no independent studies that show any of the tech ever
> worked.
>
> Ok, disprove it.
> Show me a link to the study data.
>
> "Scientology works for Scientologists as they apply it. The non-
> Scientologists who attack the Scientologists DON'T apply Scientology."
>
> How can you apply bunch of SiFI gibberish?

Since I am not living in a space opera, I can think of two ways, but
since scientology rejects drugs so vehemently, the only possibility
remaining is going insane.

Or one could just lie, say it works, and thus avoid being forced by
the ethics officer to choose between the community you have poured
your heart and wallet into and the beloved friends and family "who are
hindering your spiritual progress."

CX

haxor99

4/4/2008 1:49:00 AM

0

All that typing, just saying nothing with no facts.

All you can do is hurl insults, because when it come to facts about
Scamology, you have none.
You know ALL about the Ethics officer, and disconnection.
It would be easy to find 1000 people who would agree to that, in fact
I could easily get a current Scamolgist to agree to that, whast more
it has been published many times in your own bulletins, so why would
you even waste your time disputing that.

THE ETHICS OFFICER ORDERS YOU TO DISCONNECT.

Are you saying that is a lie?
Go ahead, I double dog dare ya.

butterflygrrrl

4/4/2008 2:14:00 AM

0

On Apr 3, 6:39 pm, Barbara Schwarz <BarbaraSchwarz2...@excite.com>
wrote:

This post of Barbara's is an example of the cult policy of "accuse
others of what you, yourself, are doing".

> I assume you are on drugs.

Translation:

"You don't agree with me (aka scientology) so there's something wrong
with you."

> You post unintelligently, and you are a waste of time.

Translation:

"You don't agree with me (aka scientology) so you are an inferior
being."

>You are biased,
> you are fanatical, you lie, and you accuse others on what you are
> guilty of.

Translation:

"I am biased, I am fanatical about scientology, I lie, and I accuse
others of what I am guilty of."

> You provided no shred of evidence that you ever were in the orgs. I
> post with my real name. It is broadly known that I am a Scientologist
> for many years.

Translation:

"I provided no shred of evidence that the tech works, because there is
none, so I am attacking you."

> Your posts show that you WERE NEVER A SCIENTOLGGIST.

Translation:

"Your posts show that you are immune to our brainwashing efforts and
so you are an inferior being."

>You post about
> Scientologists as they were unstable and anybody can order them and
> that is false and insulting.

Translation:

"Scientologists have been made mentally unstable by the practices of
the cult, and we follow any orders we are given so that we don't lose
our one chance at saving the planet."

>And you are afraid to post with your real
> name for one reason only: you have no connection with SCN, just as the
> Butterlie has not.

Translation:

"We want your name and address so we can harass you. We also want this
info from Butterflygrrl, as I have threatened to sue her multiple
times, but she won't give me her address so I can have her served,
even though all she would have to do to win is ask me who my daddy
is."

> You are just lonely hatemongers who have nothing else to do.

Translation:

"I am a lonely hatemonger with nothing else to do since the cult threw
me out."

> You just repeat the lies of other extremists.

Translation:

"I just repeat the lies of LRH."