Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2003
    Location
    KL, Malaysia
    Posts
    38

    Red face Unanswered: Instead of triggers and datalength constraint

    Is it possible to prevent MS Sql 2000 to check the datalength of an inserted column value before an instead of trigger complete?

    ALTER TRIGGER test_TRIGGER ON [dbo].[test]
    INSTEAD OF INSERT
    AS
    Declare @sys_cde as char(1)

    SELECT @sys_cde = sys_cde from inserted

    IF (DATALENGTH(@sys_cde)<>1)
    Begin
    log error into an error table
    END
    ELSE
    BEGIN
    do something useful
    END

    insert into test values ('as')


    I've got the below error message:

    Server: Msg 8152, Level 16, State 9, Line 1
    String or binary data would be truncated.
    The statement has been terminated.
    Save the dugongs!

  2. #2
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    1. A trigger should be written to allow multiple inserts.
    2. You can check like this
    IF exists(select 'x' from inserted where DATALENGTH(sys_cde)<>1)
    3. But it makes no sence, the table inserted is a temporary system view of the table test and it can store only 1 char. The error is returned before and instead of the trigger.

    Good luck !

  3. #3
    Join Date
    Mar 2003
    Location
    Bucharest, Romania
    Posts
    80
    If your column sys_cde is declared char(1), then ispaleny is right. The data is truncated from the very beginig in inserted table (inserted has the same columns as the original table for which you're trying to declare a triger)
    But now if you column accepts more than one character, you have two problems.

    First of all, In the way you constructed your triger, it will only work if you insert one row at a time (it will not function for multiple inserts -> for multiple inserts use ispaleny solution).

    The second problem, even if you want this to work with one row insertion at a time, you decalred variable @sys_cde as char(1), so the next line:

    SELECT @sys_cde = sys_cde from inserted

    will truncate the value of sys_cde field to a single char (that's all that @sys_cde var can handle), and this is where your problem came from.

    Even if SQL wouldn't throw you an error, the trigger will not work, because lenght of @sys_cde is always 1, so your condition:

    IF (DATALENGTH(@sys_cde)<>1)
    will always return FALSE, and log error code will never triger.


    IONUT

  4. #4
    Join Date
    Jan 2003
    Location
    KL, Malaysia
    Posts
    38

    Thumbs up

    Thanks for the response. Especially for reminding me about the multiple inserts.
    Save the dugongs!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •