Big problem with triggers

I have little problem and I dont have any idea how to make my trigger.

I have table MyTable where I have many column with almost same name and same
type (Grp1,Grp2,Grp3,Grp4...Grp50 char(1)).
I`d like to make some trigger on UPDATE this table. I must to check which
column was changed.

For example to check if Grp1 was changed I can try this:

ALTER TRIGGER MyTrigger
ON MyTable
AFTER UPDATE
AS
DECLARE @.OldValue char(1)
DECLARE @.NewValue char(1)
BEGIN
SET @.OldValue = (SELECT Grp1 FROM DELETED)
SET @.NewValue = (SELECT Grp1 FROM INSERTED)

IF (@.OldValue <> @.NewValue)
BEGIN
...
...
...
END
END

Of course I can do this step by step, for all columns, but it`s not good
option.
I tried to make this trigger more dynamicaly, but this not worked (Server:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'DELETED'.)

ALTER TRIGGER MyTrigger
ON MyTable
AFTER UPDATE
AS
DECLARE @.OldValue char(1)
DECLARE @.NewValue char(1)
DECLARE @.MyQry VarChar(1000)
DECLARE @.ActGrp VarChar(2)
BEGIN
@.ActGrp = '1'
SET @.MyQry ='
SET @.OldValue = (SELECT Grp'+@.ActGrp+' FROM DELETED)
SET @.NewValue = (SELECT Grp'+@.ActGrp+' FROM INSERTED)

IF (@.OldValue <> @.NewValue)
BEGIN
...
...
...
END
'
END

:(

Can anybody help me? How can I easy check all Grp1...Grp50 to know where
colums was changed?Hi

Triggers are activated on a statement and not per row changed, therefore the
use of variables in your trigger to store old and new values will not
produce predictable results.

In general it is better to keep your triggers as short as possible to avoid
prolonging your transactions. If you are trying to produce and audit trail
then I would keep the comparisons external to the trigger and follow the
example of auditing in
http://msdn.microsoft.com/library/d...create_4hk5.asp

John

"BUSHII" <piotrl@.robcom.com.pl> wrote in message
news:cbbcqa$g62$1@.atlantis.news.tpi.pl...
> I have little problem and I dont have any idea how to make my trigger.
> I have table MyTable where I have many column with almost same name and
same
> type (Grp1,Grp2,Grp3,Grp4...Grp50 char(1)).
> I`d like to make some trigger on UPDATE this table. I must to check which
> column was changed.
> For example to check if Grp1 was changed I can try this:
> ALTER TRIGGER MyTrigger
> ON MyTable
> AFTER UPDATE
> AS
> DECLARE @.OldValue char(1)
> DECLARE @.NewValue char(1)
> BEGIN
> SET @.OldValue = (SELECT Grp1 FROM DELETED)
> SET @.NewValue = (SELECT Grp1 FROM INSERTED)
>
> IF (@.OldValue <> @.NewValue)
> BEGIN
> ...
> ...
> ...
> END
> END
>
> Of course I can do this step by step, for all columns, but it`s not good
> option.
> I tried to make this trigger more dynamicaly, but this not worked (Server:
> Msg 208, Level 16, State 1, Line 1
> Invalid object name 'DELETED'.)
> ALTER TRIGGER MyTrigger
> ON MyTable
> AFTER UPDATE
> AS
> DECLARE @.OldValue char(1)
> DECLARE @.NewValue char(1)
> DECLARE @.MyQry VarChar(1000)
> DECLARE @.ActGrp VarChar(2)
> BEGIN
> @.ActGrp = '1'
> SET @.MyQry ='
> SET @.OldValue = (SELECT Grp'+@.ActGrp+' FROM DELETED)
> SET @.NewValue = (SELECT Grp'+@.ActGrp+' FROM INSERTED)
>
> IF (@.OldValue <> @.NewValue)
> BEGIN
> ...
> ...
> ...
> END
> '
> END
> :(
> Can anybody help me? How can I easy check all Grp1...Grp50 to know where
> colums was changed?sql

between

hi,
what diff:
select * from mytable where intfieldname between 1 and 5
and
select * from mytable where intfieldname between 5 and 1?
Thanks.X BETWEEN Y AND Z
is equivalent to:
X>=Y AND X<=Z
so your second example will never return any rows.
David Portas
SQL Server MVP
--|||The second will return 0 rows. The smaller value need to be the first value
you list.
(SQL Server allowed both to return rows in some prior version, it was change
d in, I believe, 6.0, to
adhere to the ANSI SQL standard.)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"js" <js@.someone@.hotmail.com> wrote in message news:%23itV%2388rFHA.3392@.TK2MSFTNGP11.phx.g
bl...
> hi,
> what diff:
> select * from mytable where intfieldname between 1 and 5
> and
> select * from mytable where intfieldname between 5 and 1?
> Thanks.
>|||can you see it?
select * from t1 where c1 >= 1 and c1 <= 5
select * from t1 where c1 >= 5 and c1 <= 1
AMB
"js" wrote:

> hi,
> what diff:
> select * from mytable where intfieldname between 1 and 5
> and
> select * from mytable where intfieldname between 5 and 1?
> Thanks.
>
>|||The Standard changed too. SQL99 defined a SYMMETRIC / ASYMMETRIC clause
for the BETWEEN predicate. ASYMMETRIC - the old behaviour - is still
the default, but:
X BETWEEN SYMMETRIC Y AND Z
is equivalent to:
((X BETWEEN ASYMMETRIC Y AND Z)
OR (X BETWEEN ASYMMETRIC Z AND Y))
David Portas
SQL Server MVP
--|||Order matters. The left side must be smaller.
My typical suggestion is to avoid between for this and other reasons...
http://www.aspfaq.com/2280
"js" <js@.someone@.hotmail.com> wrote in message
news:%23itV%2388rFHA.3392@.TK2MSFTNGP11.phx.gbl...
> hi,
> what diff:
> select * from mytable where intfieldname between 1 and 5
> and
> select * from mytable where intfieldname between 5 and 1?
> Thanks.
>|||> http://www.aspfaq.com/2280
Quote:
"When you are using other clauses in your query, you also have to
remember to wrap the BETWEEN clause in brackets so that its AND isn't
with other ANDs in the WHERE clause."
Just to be clear about this point. Parenthesizing the BETWEEN predicate
probably aids readability but has no effect on the order of evaluation.
That's because the AND keyword in the BETWEEN predicate has nothing to
do with the Boolean operator of the same name. "x BETWEEN y AND z" is
evaluated as a single expression. I mention this detail because I've
found people get similarly over the predicate "IS NOT NULL",
which is three keywords but a single expression.
David Portas
SQL Server MVP
--|||Yes, the point about evaluation was meant to apply only to the point about ,
not that x = 5 or x between 7 and 12 or x = 4 would be evaludated in some
weird way. The bullet itself was added merely to address readability
concerns, but it does bring up the notion of more complex queries. I'm not
sure how to word it better to turn off the alarm bells that caused your
reply, however...
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1125676773.780264.262550@.g49g2000cwa.googlegroups.com...
> Quote:
> "When you are using other clauses in your query, you also have to
> remember to wrap the BETWEEN clause in brackets so that its AND isn't
> with other ANDs in the WHERE clause."
> Just to be clear about this point. Parenthesizing the BETWEEN predicate
> probably aids readability but has no effect on the order of evaluation.
> That's because the AND keyword in the BETWEEN predicate has nothing to
> do with the Boolean operator of the same name. "x BETWEEN y AND z" is
> evaluated as a single expression. I mention this detail because I've
> found people get similarly over the predicate "IS NOT NULL",
> which is three keywords but a single expression.
> --
> David Portas
> SQL Server MVP
> --
>|||Thanks all for the help.|||That's news to me. Thanks David. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1125674797.272562.256010@.g47g2000cwa.googlegroups.com...
> The Standard changed too. SQL99 defined a SYMMETRIC / ASYMMETRIC clause
> for the BETWEEN predicate. ASYMMETRIC - the old behaviour - is still
> the default, but:
> X BETWEEN SYMMETRIC Y AND Z
> is equivalent to:
> ((X BETWEEN ASYMMETRIC Y AND Z)
> OR (X BETWEEN ASYMMETRIC Z AND Y))
> --
> David Portas
> SQL Server MVP
> --
>

Best way to restrict a varchar field not to contain spaces?

Hi,

is this the best way?

ALTER TABLE MyTable
ADD CONSTRAINT CK_MyTable_NoSpace
CHECK (charindex(' ', MyColumn)=0)

Is there any other way?

Thnx

Not sure but you can also try NOT LIKE '% %'

Try them both. What is the reason you need this?

|||

Thanks Simon

SimonSa wrote:

NOT LIKE '% %'

It would have to be trimmed first no?

or maybe:

NOT LIKE '% %' AND NOT LIKE '% ' AND NOT LIKE ' %'

SimonSa wrote:

What is the reason you need this?

File system entries. The OS allows them but I want to restrict them. It is also being done in the DAL.

|||

Carl M. wrote:

Thanks Simon

SimonSa wrote:

NOT LIKE '% %'

It would have to be trimmed first no?

or maybe:

NOT LIKE '% %' AND NOT LIKE '% ' AND NOT LIKE ' %'

No. % can stand for any number of characters, even none. NOT LIKE '% %' would be correct.

|||Thank you.