Trigger to not working

18 May 2005 6:51 AM

EC

I need help/suggestion/hint to fix a tigger that is NOT working correctly if
more than one record are inserted/update at a time. My problem is that the
same value is set for the ShortDescription column. I thought that the
trigger would fire for every row inserted but I obviously am wrong. Below is
a simplified version of my DDL and codes:

-------------------------------
CREATE TABLE T1
(
Details varchar(2000),
ShortDescription varchar(60)
)
---------------------------
CREATE TRIGGER GetShortDescription ON T1
FOR INSERT, UPDATE
AS
    IF UPDATE(Details)
    BEGIN
        DECLARE @Details VARCHAR(2000)
        SELECT @Details = (SELECT Details FROM Inserted)

        IF NOT @Details IS NULL
        BEGIN

            DECLARE @ShortDesc VARCHAR(200)

            SELECT @ShortDesc = SUBSTRING(@Details, 1, 200)

            UPDATE T1
            SET ShortDescription = @ShortDesc

        END
    END

18 May 2005 6:59 AM

Uri Dimant

EC
Are you sure that INSERTED virtual table contains only one row?. Check for
multi-insert/update

Show quote

"EC" <edw***@mailblocks.com> wrote in message
news:OOVvNY3WFHA.628@tk2msftngp13.phx.gbl...
> I need help/suggestion/hint to fix a tigger that is NOT working correctly
if
> more than one record are inserted/update at a time. My problem is that
the
> same value is set for the ShortDescription column. I thought that the
> trigger would fire for every row inserted but I obviously am wrong. Below
is
> a simplified version of my DDL and codes:
>
> -------------------------------
> CREATE TABLE T1
> (
> Details varchar(2000),
> ShortDescription varchar(60)
> )
> ---------------------------
> CREATE TRIGGER GetShortDescription ON T1
> FOR INSERT, UPDATE
> AS
>     IF UPDATE(Details)
>     BEGIN
>         DECLARE @Details VARCHAR(2000)
>         SELECT @Details = (SELECT Details FROM Inserted)
>
>         IF NOT @Details IS NULL
>         BEGIN
>
>             DECLARE @ShortDesc VARCHAR(200)
>
>             SELECT @ShortDesc = SUBSTRING(@Details, 1, 200)
>
>             UPDATE T1
>             SET ShortDescription = @ShortDesc
>
>         END
>     END
>
>
>
>

18 May 2005 7:11 AM

Chandra

Hi EC
Try to do it this way

UPDATE T1
SET ShortDescription = INSERETD.Details
FROM T1
INNER JOIN INSERTED ON INSERTED.<KEY> = T1.KEY

TRIGGER is per statement and not per row

--
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---------------------------------------

Show quote

"EC" wrote:

> I need help/suggestion/hint to fix a tigger that is NOT working correctly if
> more than one record are inserted/update at a time. My problem is that the
> same value is set for the ShortDescription column. I thought that the
> trigger would fire for every row inserted but I obviously am wrong. Below is
> a simplified version of my DDL and codes:
>
> -------------------------------
> CREATE TABLE T1
> (
> Details varchar(2000),
> ShortDescription varchar(60)
> )
> ---------------------------
> CREATE TRIGGER GetShortDescription ON T1
> FOR INSERT, UPDATE
> AS
>     IF UPDATE(Details)
>     BEGIN
>         DECLARE @Details VARCHAR(2000)
>         SELECT @Details = (SELECT Details FROM Inserted)
>
>         IF NOT @Details IS NULL
>         BEGIN
>
>             DECLARE @ShortDesc VARCHAR(200)
>
>             SELECT @ShortDesc = SUBSTRING(@Details, 1, 200)
>
>             UPDATE T1
>             SET ShortDescription = @ShortDesc
>
>         END
>     END
>
>
>
>
>

18 May 2005 7:16 AM

John Bell

Hi

To add to Uri's reply, this is untested

CREATE TRIGGER GetShortDescription ON T1
FOR INSERT, UPDATE
AS
BEGIN
       IF @@ROWCOUNT = 0 RETURN
       SET NOCOUNT ON

      IF UPDATE(Details)
      BEGIN
          UPDATE T
          SET ShortDescription = LEFT(I.Details,200)
          FROM T1 T
          JOIN INSERTED I ON I.PK = T.PK AND I.Details <> T.Details

     END
END

PK is the primary key column(s) so that you can identify which rows have
been updated.

Alternatively you could create a computed column or use a view to give the
first 200 characters. If you are only accessing the application through
stored procedures then you may not bother creating the column at all.

John
Show quote

"EC" wrote:

> I need help/suggestion/hint to fix a tigger that is NOT working correctly if
> more than one record are inserted/update at a time. My problem is that the
> same value is set for the ShortDescription column. I thought that the
> trigger would fire for every row inserted but I obviously am wrong. Below is
> a simplified version of my DDL and codes:
>
> -------------------------------
> CREATE TABLE T1
> (
> Details varchar(2000),
> ShortDescription varchar(60)
> )
> ---------------------------
> CREATE TRIGGER GetShortDescription ON T1
> FOR INSERT, UPDATE
> AS
>     IF UPDATE(Details)
>     BEGIN
>         DECLARE @Details VARCHAR(2000)
>         SELECT @Details = (SELECT Details FROM Inserted)
>
>         IF NOT @Details IS NULL
>         BEGIN
>
>             DECLARE @ShortDesc VARCHAR(200)
>
>             SELECT @ShortDesc = SUBSTRING(@Details, 1, 200)
>
>             UPDATE T1
>             SET ShortDescription = @ShortDesc
>
>         END
>     END
>
>
>
>
>

18 May 2005 9:21 AM

EC

You and Chandra help me get in the right direction by pointing out that
Tigger is per statement as opposed to per row. Using John's codes (minus the
'AND I.Details <> T.Details' clause, which seems to be False all the times
in my testings ), I now have the ShortDescription is updated as I described
in my earlier post.

One problem, which I neglected to say last time, however is that I don't
want the Details field chopped off in the middle of a word to fit into
ShortDescription lenght. So would you please have a quick look at my
original codes below, which were based on my ignorance that Trigger is fired
on every row inserted/updated, and give me some idea or hint how to fix it?
(BTW, I like your use of LEFT instead of SUBSTRING).

CREATE TRIGGER GetShortDescription_T
ON Items
FOR INSERT, UPDATE
AS
    IF UPDATE(Description)
    BEGIN
        DECLARE @Description VARCHAR(2000)
        SELECT @Description = (SELECT Description FROM Inserted)

        IF NOT @Description IS NULL
        BEGIN

            SET NOCOUNT ON

            DECLARE @ShortDesc VARCHAR(200)
            DECLARE @TempDescription VARCHAR(200)
            DECLARE @Length         INT
            DECLARE @SpaceLocation INT

            SELECT @Length = DATALENGTH(@Description)
            IF @Length < 196
                SELECT @ShortDesc = @Description
            ELSE
            BEGIN
                SELECT @TempDescription = REVERSE(SUBSTRING(@Description, 1,
196))
                SELECT @SpaceLocation = CHARINDEX(' ', @TempDescription)

                IF @SpaceLocation = 0
                    SELECT @ShortDesc = SUBSTRING(@Description, 1, 196)
                ELSE
                    SELECT @ShortDesc = REVERSE(SUBSTRING(@TempDescription,
@SpaceLocation, 197 - @SpaceLocation ))

               SELECT @ShortDesc = @ShortDesc + '...'
            END

            UPDATE Items
            SET    ShortDescription = @ShortDesc

        END
    END
------------------------------------------------

18 May 2005 11:52 AM

John Bell

Hi

The check for details being changed should stop updates where they are
replaced with the same string. You could restrict that to the first 200
characters.

Assuming that you want the last space to be the delimiter:

CREATE TRIGGER GetShortDescription ON T1
FOR INSERT, UPDATE
AS
BEGIN
       IF @@ROWCOUNT = 0 RETURN
       SET NOCOUNT ON

      DECLARE @len int
      SET @len = 200

      IF UPDATE(Details)
      BEGIN
          UPDATE T
          SET ShortDescription = LEFT(s, CASE WHEN charindex(space(1),
s, @len) = @len+1 THEN @len
            WHEN charindex(space(1), reverse(left(s,@len))) > 0 THEN @len -
charindex(space(1), reverse(left(s,@len)))
            ELSE 0 END)
          FROM T1 T
          JOIN INSERTED I ON I.PK = T.PK AND LEFT(I.Details,200) <>
LEFT(T.Details,200)

     END
END

John

18 May 2005 12:19 PM

John Bell

Hi

I forgot to substitute the s for I.Details!!!

CREATE TRIGGER GetShortDescription ON T1
FOR INSERT, UPDATE
AS
BEGIN
       IF @@ROWCOUNT = 0 RETURN
       SET NOCOUNT ON
    DECLARE @len int
    SET @len = 200
      IF UPDATE(Details)
      BEGIN
          UPDATE T
          SET ShortDescription = LEFT(I.Details, CASE WHEN
CHARINDEX(SPACE(1), I.Details, @len) = @len+1 THEN @len
            WHEN CHARINDEX(SPACE(1), REVERSE(LEFT(I.Details,@len))) > 0 THEN @len -
CHARINDEX(SPACE(1), REVERSE(LEFT(I.Details,@len)))
            ELSE 0 END)
          FROM T1 T
          JOIN INSERTED I ON I.PK = T.PK AND LEFT(I.Details,@len) <>
LEFT(T.Details,@len)

     END
END

John

Show quote

"EC" wrote:

> You and Chandra help me get in the right direction by pointing out that
> Tigger is per statement as opposed to per row. Using John's codes (minus the
> 'AND I.Details <> T.Details' clause, which seems to be False all the times
> in my testings ), I now have the ShortDescription is updated as I described
> in my earlier post.
>
> One problem, which I neglected to say last time, however is that I don't
> want the Details field chopped off in the middle of a word to fit into
> ShortDescription lenght. So would you please have a quick look at my
> original codes below, which were based on my ignorance that Trigger is fired
> on every row inserted/updated, and give me some idea or hint how to fix it?
> (BTW, I like your use of LEFT instead of SUBSTRING).
>
> CREATE TRIGGER GetShortDescription_T
> ON Items
> FOR INSERT, UPDATE
> AS
>     IF UPDATE(Description)
>     BEGIN
>         DECLARE @Description VARCHAR(2000)
>         SELECT @Description = (SELECT Description FROM Inserted)
>
>         IF NOT @Description IS NULL
>         BEGIN
>
>             SET NOCOUNT ON
>
>             DECLARE @ShortDesc VARCHAR(200)
>             DECLARE @TempDescription VARCHAR(200)
>             DECLARE @Length         INT
>             DECLARE @SpaceLocation INT
>
>             SELECT @Length = DATALENGTH(@Description)
>             IF @Length < 196
>                 SELECT @ShortDesc = @Description
>             ELSE
>             BEGIN
>                 SELECT @TempDescription = REVERSE(SUBSTRING(@Description, 1,
> 196))
>                 SELECT @SpaceLocation = CHARINDEX(' ', @TempDescription)
>
>                 IF @SpaceLocation = 0
>                     SELECT @ShortDesc = SUBSTRING(@Description, 1, 196)
>                 ELSE
>                     SELECT @ShortDesc = REVERSE(SUBSTRING(@TempDescription,
> @SpaceLocation, 197 - @SpaceLocation ))
>
>                SELECT @ShortDesc = @ShortDesc + '...'
>             END
>
>             UPDATE Items
>             SET    ShortDescription = @ShortDesc
>
>         END
>     END
> ------------------------------------------------
>
>
>

18 May 2005 2:31 PM

EC

Thank you very much for the codes that solve the problem I was facing almost
the whole day yesterday. I know I would never be able to write this kind of
codes. So I just wake up to a great gift this morning! Again I have to drop
the 'AND LEFT(I.Details,@len) <> LEFT(T.Details,@len)' clause to get the
codes to work in my insert/update testings. The clause looks like a good way
to avoid unecesssay updating.

I just tried this simple DDL, changed the set SET @len = 30 (for testing
purpose) and used the following sql statments:
insert t1(pk, details) values(1, 'a long string here. 3453 asdsa dsfdsfgd')
insert t1(pk, details) values(2, 'another long detalishere rtret rett')

I always have NULL for shortdescription if I don't left out the above
clause. The same NULL for shortdescription in my tests of update statements.

Again, thank you very much.

Show quote

"John Bell" <jbellnewspo***@h0tmail.com> wrote in message
news:95EDCF43-A47E-49C6-84CC-ACE46B86E79C@microsoft.com...
> Hi
>
> I forgot to substitute the s for I.Details!!!
>
> CREATE TRIGGER GetShortDescription ON T1
> FOR INSERT, UPDATE
> AS
> BEGIN
>       IF @@ROWCOUNT = 0 RETURN
>       SET NOCOUNT ON
> DECLARE @len int
> SET @len = 200
>      IF UPDATE(Details)
>      BEGIN
>          UPDATE T
>          SET ShortDescription = LEFT(I.Details, CASE WHEN
> CHARINDEX(SPACE(1), I.Details, @len) = @len+1 THEN @len
> WHEN CHARINDEX(SPACE(1), REVERSE(LEFT(I.Details,@len))) > 0 THEN @len -
> CHARINDEX(SPACE(1), REVERSE(LEFT(I.Details,@len)))
> ELSE 0 END)
>          FROM T1 T
>          JOIN INSERTED I ON I.PK = T.PK AND LEFT(I.Details,@len) <>
> LEFT(T.Details,@len)
>
>     END
> END
>
> John

18 May 2005 7:54 AM

David Lightman Robles

Since you are updating the same table that the trigger is fired on, have you
tried using INSTEAD OF triggers?
You will need to write 3 different triggers for INSERT, UPDATE and DELETE
but you will be able to manage that ShortDesc column as you like.

Show quote

"EC" <edw***@mailblocks.com> escribió en el mensaje
news:OOVvNY3WFHA.628@tk2msftngp13.phx.gbl...
>I need help/suggestion/hint to fix a tigger that is NOT working correctly
>if more than one record are inserted/update at a time. My problem is that
>the same value is set for the ShortDescription column. I thought that the
>trigger would fire for every row inserted but I obviously am wrong. Below
>is a simplified version of my DDL and codes:
>
> -------------------------------
> CREATE TABLE T1
> (
> Details varchar(2000),
> ShortDescription varchar(60)
> )
> ---------------------------
> CREATE TRIGGER GetShortDescription ON T1
> FOR INSERT, UPDATE
> AS
>    IF UPDATE(Details)
>    BEGIN
>        DECLARE @Details VARCHAR(2000)
>        SELECT @Details = (SELECT Details FROM Inserted)
>
>        IF NOT @Details IS NULL
>        BEGIN
>
>            DECLARE @ShortDesc VARCHAR(200)
>
>            SELECT @ShortDesc = SUBSTRING(@Details, 1, 200)
>
>            UPDATE T1
>            SET ShortDescription = @ShortDesc
>
>        END
>    END
>
>
>
>

18 May 2005 9:23 AM

EC

As you suggested, I am reading the book Online about them now, but they are
seem to be way above my comprehension at the momoent. If only I knew how to
use INSTEAD OF triggers :-(
Thanks.

Show quote

"David Lightman Robles" <dlightman@NOSPAMiname.com> wrote in message
news:O5AKj73WFHA.2256@TK2MSFTNGP14.phx.gbl...
> Since you are updating the same table that the trigger is fired on, have
> you tried using INSTEAD OF triggers?
> You will need to write 3 different triggers for INSERT, UPDATE and DELETE
> but you will be able to manage that ShortDesc column as you like.
>
> "EC" <edw***@mailblocks.com> escribió en el mensaje
> news:OOVvNY3WFHA.628@tk2msftngp13.phx.gbl...
>>I need help/suggestion/hint to fix a tigger that is NOT working correctly
>>if more than one record are inserted/update at a time. My problem is that
>>the same value is set for the ShortDescription column. I thought that the
>>trigger would fire for every row inserted but I obviously am wrong. Below
>>is a simplified version of my DDL and codes:
>>
>> -------------------------------
>> CREATE TABLE T1
>> (
>> Details varchar(2000),
>> ShortDescription varchar(60)
>> )
>> ---------------------------
>> CREATE TRIGGER GetShortDescription ON T1
>> FOR INSERT, UPDATE
>> AS
>>    IF UPDATE(Details)
>>    BEGIN
>>        DECLARE @Details VARCHAR(2000)
>>        SELECT @Details = (SELECT Details FROM Inserted)
>>
>>        IF NOT @Details IS NULL
>>        BEGIN
>>
>>            DECLARE @ShortDesc VARCHAR(200)
>>
>>            SELECT @ShortDesc = SUBSTRING(@Details, 1, 200)
>>
>>            UPDATE T1
>>            SET ShortDescription = @ShortDesc
>>
>>        END
>>    END
>>
>>
>>
>>
>
>

19 May 2005 6:40 AM

John Bell

Hi

This is an example of the instead of insert trigger:

create table t1 ( pk int, details varchar(8000), shortdescription
varchar(30) )

CREATE TRIGGER GetShortDescription ON T1
INSTEAD OF INSERT
AS
BEGIN
       IF @@ROWCOUNT = 0 RETURN
       SET NOCOUNT ON
        DECLARE @len int
        SET @len = 30

    INSERT INTO T1 ( pk, details, shortdescription )
    SELECT I.pk, details, LEFT (I.Details, CASE
        WHEN CHARINDEX(SPACE(1), I.Details, @len) = @len+1 THEN @len
        WHEN CHARINDEX(SPACE(1), REVERSE(LEFT(I.Details,@len))) > 0 THEN @len
-
CHARINDEX(SPACE(1), REVERSE(LEFT(I.Details,@len)))
                        ELSE 0 END)
          FROM INSERTED I
END

insert t1(pk, details)
SELECT 1, 'a long string here. 3453 asdsa dsfdsfgd'
UNION ALL SELECT 2, 'another long detalishere rtret rett'
UNION ALL SELECT 3, 'another long detalishere rtretrett'
UNION ALL SELECT 4, 'another long detalishere rtretr ett'
UNION ALL SELECT 5, 'another long detalishere rtre trett'

SELECT * FROM T1

John