Sunday, March 25, 2012

@ and @@ in a trigger

Hi,
I saw the @. and @.@. in a SQL Server trigger, would some tell me what they
are?
Thanks for help.
Jason
@. indicates a local variable or parameter.
@.@. indicates a class of parameter-less system functions, such as @.@.rowcount,
which returns the number or rows affected by the most recent command.
You can read about local variables and parameters in the Books Online.
If you go to the BOL index, you will see all the @.@. functions listed
together, and you can see what they all are.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
news:eKw4WLQOGHA.3576@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I saw the @. and @.@. in a SQL Server trigger, would some tell me what they
> are?
> Thanks for help.
>
> Jason
>
|||Thanks Kalen!
"Kalen Delaney" <replies@.public_newsgroups.com> glsD:u84AT6QOGHA.964@.tk2msftngp13.phx.gb l...
> @. indicates a local variable or parameter.
> @.@. indicates a class of parameter-less system functions, such as
> @.@.rowcount, which returns the number or rows affected by the most recent
> command.
> You can read about local variables and parameters in the Books Online.
> If you go to the BOL index, you will see all the @.@. functions listed
> together, and you can see what they all are.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
> news:eKw4WLQOGHA.3576@.TK2MSFTNGP15.phx.gbl...
>
>
|||Jason Huang a crit :
> Hi,
> I saw the @. and @.@. in a SQL Server trigger, would some tell me what they
> are?
> Thanks for help.
>
> Jason
>
If you see thoses characters in a trigger code, the is a good chance
that this trigger wont work properly...
Why ?
Because a trigger id fired only once even if the SQL statement that fire
it, had to update, insert or delete on million row.
So variables to get some datas in pseudo tables (inserted, deleted) ...
will only works for one row of the set...
Wich mean that this trigger will not produce its effects on n-1 other
rows...
A +
PS : sorry for my bad english
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************
|||I don't agree.
It is EXTREMELY common to check @.@.rowcount as soon as the trigger starts, to
verify how many rows were affected, and if it is more than 1, then we can
rollback or process the data in a different way. And since @.@.rowcount
changes every time you look at it, you may want to capture it into your own
@. variable.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"SQLpro [MVP]" <brouardf@.club-internet.fr> wrote in message
news:%23iXktcROGHA.2268@.TK2MSFTNGP09.phx.gbl...
> Jason Huang a crit :
> If you see thoses characters in a trigger code, the is a good chance that
> this trigger wont work properly...
> Why ?
> Because a trigger id fired only once even if the SQL statement that fire
> it, had to update, insert or delete on million row.
> So variables to get some datas in pseudo tables (inserted, deleted) ...
> will only works for one row of the set...
> Wich mean that this trigger will not produce its effects on n-1 other
> rows...
> A +
> PS : sorry for my bad english
> --
> Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
> Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
> Audit, conseil, expertise, formation, modlisation, tuning, optimisation
> ********************* http://www.datasapiens.com ***********************
>

No comments:

Post a Comment