I want to create a trigger on a SQL Server table.
Table has 8 columns. One of them is email. i.e. [email protected]
What I want is: when a new row is inserted into the table, the trigger should update my ISP column with msn.com
CREATE TRIGGER TIGUPDATEISP ON SUBS
FOR INSERT
AS
UPDATE SUBS
SET ISP = SELECT RIGHT(EMAIL, LEN(EMAIL) - CHARINDEX('@', EMAIL) FROM INSERTED)
This is updating all my isp with the last value. However, I wanted to update one as per insert.
This will work if the ISP column allows NULL values
CREATE TRIGGER TIGUPDATEISP ON Subs
FOR INSERT
AS
BEGIN
SET NOCOUNT ON;
UPDATE SUBS
SET ISP = (SELECT RIGHT(EMAIL, LEN(EMAIL) - CHARINDEX('@', EMAIL))
FROM INSERTED
WHERE SUBS.ID = INSERTED.ID)
FROM INSERTED
WHERE SUBS.Id = INSERTED.ID
END
This will work if the ISP column doesn't allow NULLS
CREATE TRIGGER TIGUPDATEISP ON Subs
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON;
INSERT Subs (EMAIL, Isp)
SELECT EMAIL, RIGHT(EMAIL, LEN(EMAIL) - CHARINDEX('@', EMAIL)) FROM INSERTED
END
