Results 1 to 13 of 13
  1. #1
    Join Date
    Dec 2003
    Posts
    16

    Unanswered: constraints for e-mail

    Is it possible to write a check constraint for a column in a table which says that the entry must contain a '@' and a '.'? (It's an email address field)

    I'm guessing this is the best way to do it anyway,

    Thanks in advance, newbie!

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Code:
    USE Northwind
    GO
    
    CREATE TABLE myTable99(
    	Col1 varchar(255) 
    	CONSTRAINT myTable99_ck1 
    		CHECK  (    LEN(Col1)=LEN(REPLACE(Col1,'@',''))+1
    			AND LEN(Col1)>LEN(REPLACE(Col1,'.',''))
    			)
    )
    
    INSERT INTO myTable99(Col1) SELECT 'brett.kaiser@somewhere.com'
    INSERT INTO myTable99(Col1) SELECT 'brettkaiser@somewhere'
    INSERT INTO myTable99(Col1) SELECT 'brett.kaiser somewhere.com'
    
    SELECT * FROM myTable99
    GO
    
    DROP TABLE myTable99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What? You don't like it?

    :-o
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    How about

    check (col1 like '%@%.%')

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by MCrowley
    How about

    check (col1 like '%@%.%')
    That'll allow more than 1 @ sign...

    Code:
    CREATE TABLE myTable99(
    	Col1 varchar(255) 
    	CONSTRAINT myTable99_ck1 
    		CHECK  (    (col1 like '%@%.%')
    			)
    )
    
    INSERT INTO myTable99(Col1) SELECT 'brett.kaiser@somewhere.com'
    INSERT INTO myTable99(Col1) SELECT 'brett@kaiser@somewhere.com'
    INSERT INTO myTable99(Col1) SELECT 'brettkaiser@somewhere'
    INSERT INTO myTable99(Col1) SELECT 'brett.kaiser somewhere.com'
    
    SELECT * FROM myTable99
    GO
    
    DROP TABLE myTable99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Dec 2003
    Posts
    16
    thanks guys thats great!

    sorry i took so long to get back

  7. #7
    Join Date
    Dec 2003
    Posts
    16

    TRIGGER!

    While i'm on..

    could anyone help me with this trigger!!

    Create Trigger Maintain_employeedata
    On payroll_tbl
    After update on employee_tbl
    For Each Row
    Begin
    Insert into payroll_tbl
    (payroll_id, employee_id)
    Values
    (:New.payroll_id, :Old.employee_id);
    End;

    This does not seem to work, it produces the following errors:

    Server: Msg 156, Level 15, State 1, Procedure Maintain_SurveyorData, Line 3
    Incorrect syntax near the keyword 'on'.
    Server: Msg 170, Level 15, State 1, Procedure Maintain_SurveyorData, Line 9
    Line 9: Incorrect syntax near ':'.

    Basically when I add a new employee to my employee table it should add this employee to the payroll table via their employee_id.. and subsequently create a new payroll_id to match. the payroll_id is the primary key of the table and should be an increment of the last one (eg if we have got up to P0007, it should create P0008 for the new person).

    Is this actually possible? its really bugging me!

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    First it looks like you have an Oracle background...

    Second, you're inserting in to the same table, after an update to the sane table...I don;t understand this..

    Third SQL does not have a FOR EACH ROW syntax, you have to join to the virtual table "inserted" (oracles new) and "deleted (oracles old)..

    fourth, the error message is for something else..

    CREATE TRIGGER <triiger_name> ON Table

    is correct

    AFTER UPDATE (which isn't required, is the default) doesn't use the ON Table syntax....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Dec 2003
    Posts
    16
    ah, well actually I don't have a background in any form! that was just syntax i picked up, and i'm only working on MS SQL server using the query analyzer :|

    I have these ammendments, but I don't think ive quite grasped what youre saying?

    Create Trigger Maintain_employeedata on payroll_tbl
    After update on employee_tbl
    For Each Row
    Begin
    Insert into payroll_tbl
    (payroll_id, employee_id)
    Values
    (:inserted.employee_id, :deleted.payroll_id);
    End;

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I don't pretend to understand what you're trying to do...

    but at least this should compile....

    You need to make sure you identify the key of the row...is it employeeId?

    Code:
    CREATE TRIGGER Maintain_employeedata ON Employee_tbl
    FOR UPDATE
    BEGIN
    INSERT INTO payroll_tbl (payroll_id, employee_id)
         SELECT i.employee_id, d.payroll_id
           FROM inserted i
     INNER JOIN deleted d
    	 ON i.key of the row = d.key of the row
    END
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Dec 2003
    Posts
    16
    yeah ive altered the keys so that they are right. i.employee_id and d.payroll_id

    but i have

    Server: Msg 156, Level 15, State 1, Procedure Maintain_employeedata, Line 3
    Incorrect syntax near the keyword 'BEGIN'.

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    [doooh]
    I forgot the AS...place in a line before the BEGIN
    [/doooh]
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  13. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    It's actually easier to help if you post the DDL of the table, and some sample data, and some sample DML (The Updates statements)

    Sample data should look like

    INSERT INTO myTable99(col1,col2,col3,ect)
    SELECT 'a',1','x',ect UINION ALL
    SELECT 'a',1','x',ect UINION ALL
    SELECT 'a',1','x',ect UINION ALL
    SELECT 'a',1','x',ect UINION ALL
    SELECT 'a',1','x',ect UINION ALL
    ect


    DDL looks like

    CREATE TABEL mtTable99 (Col1, char(1), col2 int, ect...

    You'll get answers that are correct, and fatse that way...

    MOO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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