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!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|||What? You don't like it?

:-o|||How about

check (col1 like '%@.%.%')|||Originally posted by MCrowley
How about

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

That'll allow more than 1 @. sign...

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|||thanks guys thats great! :)

sorry i took so long to get back|||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!|||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...|||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;|||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?

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|||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'.|||I forgot the AS...place in a line before the BEGIN|||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