Professional Web Applications Themes

Trigger with a function (how to) - Microsoft SQL / MS SQL Server

Hello, I was wondering how to create a function withing a trigger (Sql Server 2000). I would like to put the invalid email characters in a function so it is not ted (see the code snippet below) An example would be great! Thanks in advanced! CREATE TRIGGER dbo.trg_d_EmailSend_Insert ON dbo.d_EmailSend FOR INSERT As SELECT ApplicationId = Application_id, EmailId = Send_id, ToTx = Recipients_tx, FromTx = From_tx, SubjectTx = Subject_tx FROM Inserted -- check the TO: email field ELSE IF ToTx LIKE '%[ ,$"`!#a[a]a%^&*()={}|\:;<>/?]''%' ESCAPE 'a' OR ToTx NOT LIKE '%__%._%' -- normal email pattern OR ToTx LIKE '%..%' -- mistaken ...

  1. #1

    Default Trigger with a function (how to)

    Hello,

    I was wondering how to create a function withing a trigger (Sql Server 2000). I would like to put the invalid email characters in a function so it is not ted (see the code snippet below)

    An example would be great!

    Thanks in advanced!



    CREATE TRIGGER dbo.trg_d_EmailSend_Insert ON dbo.d_EmailSend
    FOR INSERT

    As

    SELECT ApplicationId = Application_id,
    EmailId = Send_id,
    ToTx = Recipients_tx,
    FromTx = From_tx,
    SubjectTx = Subject_tx
    FROM Inserted


    -- check the TO: email field
    ELSE IF ToTx

    LIKE '%[ ,$"`!#a[a]a%^&*()={}|\:;<>/?]''%' ESCAPE 'a'
    OR ToTx NOT LIKE '%__%._%' -- normal email pattern
    OR ToTx LIKE '%..%' -- mistaken .. for .
    OR ToTx LIKE '%%%' -- containing 2 signs
    OR ToTx LIKE '%' -- ends with the missing domain and ID
    OR ToTx LIKE '%' -- begins with the missing name
    OR ToTx LIKE '%.' -- ends with .
    OR ToTx LIKE '' -- is totally blank
    OR ToTx LIKE '%.%' -- missing name
    OR ToTx LIKE '%.%' -- missing domain
    OR ToTx LIKE '%''%' -- contains '
    OR ToTx LIKE '% %' -- contains blank

    BEGIN

    SELECT appIicationId = CAST( ApplicationId AS varchar(64))
    SELECT buff = ToSubjectError + ToTx + ' (Application Id: ' + appIicationId + ')'

    UPDATE d_EmailSend SET Recipients_tx = FromTx,
    Subject_tx =buff
    WHERE Send_id = EmailId

    END



    -- check the FROM: email field
    ELSE IF FromTx

    LIKE '%[ ,$"`!#a[a]a%^&*()={}|\:;<>/?]''%' ESCAPE 'a'
    OR FromTx NOT LIKE '%__%._%' -- normal email pattern
    OR FromTx LIKE '%..%' -- mistaken .. for .
    OR FromTx LIKE '%%%' -- containing 2 signs
    OR FromTx LIKE '%' -- ends with the missing domain and ID
    OR FromTx LIKE '%' -- begins with the missing name
    OR FromTx LIKE '%.' -- ends with .
    OR FromTx LIKE '' -- is totally blank
    OR FromTx LIKE '%.%' -- missing name
    OR FromTx LIKE '%.%' -- missing domain
    OR FromTx LIKE '% %' -- contains blank
    OR FromTx LIKE '%''%' -- contains '

    BEGIN

    SELECT appIicationId = CAST( ApplicationId AS varchar(64))
    SELECT buff = FromSubjectError + ToTx + ' (Application Id: ' + appIicationId + ')'

    UPDATE d_EmailSend SET Recipients_tx = DefaultFromEmail,
    Subject_tx =buff,
    From_tx = DefaultFromEmail
    WHERE Send_id = EmailId

    END



    ************************************************** ********************
    Sent via Fuzzy Software [url]http://www.fuzzysoftware.com/[/url]
    Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
    aylwinagena@hotmail.com Guest

  2. #2

    Default Re: Trigger with a function (how to)

    CREATE FUNCTION dbo.IsEmailAddress (text varchar(1000)) RETURNS bit AS
    BEGIN RETURN (
    CASE WHEN text LIKE '%[ ,$"`!#a[a]a%^&*()={}|\:;<>/?]''%' ESCAPE 'a'
    OR text NOT LIKE '%__%._%' -- normal email pattern
    OR text LIKE '%..%' -- mistaken .. for .
    OR text LIKE '%%%' -- containing 2 signs
    OR text LIKE '%' -- ends with the missing
    domain and ID
    OR text LIKE '%' -- begins with the missing
    name
    OR text LIKE '%.' -- ends with .
    OR text LIKE '' -- is totally blank
    OR text LIKE '%.%' -- missing name
    OR text LIKE '%.%' -- missing domain
    OR text LIKE '%''%' -- contains '
    OR text LIKE '% %' -- contains blank
    THEN 0
    ELSE 1
    END
    ) END
    GO

    IF dbo.IsEmailAddress('userdomain.com') = 1
    PRINT 'valid'
    ELSE
    PRINT 'isvalid'
    GO

    "aylwin agena" <aylwinagenahotmail.com> wrote in message
    news:#vFApwBQDHA.3016TK2MSFTNGP10.phx.gbl...
    > Hello,
    >
    > I was wondering how to create a function withing a trigger (Sql Server
    2000). I would like to put the invalid email characters in a function so it
    is not ted (see the code snippet below)
    >
    > An example would be great!
    >
    > Thanks in advanced!
    >
    >
    >
    > CREATE TRIGGER dbo.trg_d_EmailSend_Insert ON dbo.d_EmailSend
    > FOR INSERT
    >
    > As
    >
    > SELECT ApplicationId = Application_id,
    > EmailId = Send_id,
    > ToTx = Recipients_tx,
    > FromTx = From_tx,
    > SubjectTx = Subject_tx
    > FROM Inserted
    >
    >
    > -- check the TO: email field
    > ELSE IF ToTx
    >
    > LIKE '%[ ,$"`!#a[a]a%^&*()={}|\:;<>/?]''%' ESCAPE 'a'
    > OR ToTx NOT LIKE '%__%._%' -- normal email pattern
    > OR ToTx LIKE '%..%' -- mistaken .. for .
    > OR ToTx LIKE '%%%' -- containing 2 signs
    > OR ToTx LIKE '%' -- ends with the missing
    domain and ID
    > OR ToTx LIKE '%' -- begins with the missing
    name
    > OR ToTx LIKE '%.' -- ends with .
    > OR ToTx LIKE '' -- is totally blank
    > OR ToTx LIKE '%.%' -- missing name
    > OR ToTx LIKE '%.%' -- missing domain
    > OR ToTx LIKE '%''%' -- contains '
    > OR ToTx LIKE '% %' -- contains blank
    >
    > BEGIN
    >
    > SELECT appIicationId = CAST( ApplicationId AS varchar(64))
    > SELECT buff = ToSubjectError + ToTx + ' (Application Id: ' +
    appIicationId + ')'
    >
    > UPDATE d_EmailSend SET Recipients_tx = FromTx,
    > Subject_tx =buff
    > WHERE Send_id = EmailId
    >
    > END
    >
    >
    >
    > -- check the FROM: email field
    > ELSE IF FromTx
    >
    > LIKE '%[ ,$"`!#a[a]a%^&*()={}|\:;<>/?]''%' ESCAPE 'a'
    > OR FromTx NOT LIKE '%__%._%' -- normal email pattern
    > OR FromTx LIKE '%..%' -- mistaken .. for .
    > OR FromTx LIKE '%%%' -- containing 2 signs
    > OR FromTx LIKE '%' -- ends with the missing
    domain and ID
    > OR FromTx LIKE '%' -- begins with the
    missing name
    > OR FromTx LIKE '%.' -- ends with .
    > OR FromTx LIKE '' -- is totally blank
    > OR FromTx LIKE '%.%' -- missing name
    > OR FromTx LIKE '%.%' -- missing domain
    > OR FromTx LIKE '% %' -- contains blank
    > OR FromTx LIKE '%''%' -- contains '
    >
    > BEGIN
    >
    > SELECT appIicationId = CAST( ApplicationId AS varchar(64))
    > SELECT buff = FromSubjectError + ToTx + ' (Application Id: ' +
    appIicationId + ')'
    >
    > UPDATE d_EmailSend SET Recipients_tx = DefaultFromEmail,
    > Subject_tx =buff,
    > From_tx = DefaultFromEmail
    > WHERE Send_id = EmailId
    >
    > END
    >
    >
    >
    > ************************************************** ********************
    > Sent via Fuzzy Software [url]http://www.fuzzysoftware.com/[/url]
    > Comprehensive, categorised, searchable collection of links to ASP &
    ASP.NET resources...


    Anthony Faull Guest

Similar Threads

  1. running function after XMLConnector.trigger is complete
    By cbprice in forum Macromedia Flash Data Integration
    Replies: 1
    Last Post: October 6th, 07:18 AM
  2. Problem creating trigger-function with arguments (8.0rc4)
    By Michael Fuhr in forum PostgreSQL / PGSQL
    Replies: 5
    Last Post: January 7th, 11:08 PM
  3. Trigger Help
    By Mike in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 9th, 07:28 PM
  4. note 33601 deleted from function.trigger-error by alindeman
    By alindeman@php.net in forum PHP Notes
    Replies: 0
    Last Post: July 1st, 06:11 PM
  5. note 33601 added to function.trigger-error
    By php-general@lists.php.net in forum PHP Notes
    Replies: 0
    Last Post: July 1st, 05:57 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139