September 13, 2002 at 12:45 pm
This trigger
CREATE TRIGGER Update_Foro_Member
ON [dbo].[tblCliente]
AFTER UPDATE
AS
IF NOT UPDATE (Cl_Estado)
BEGIN
RETURN
END
IF NOT EXISTS (SELECT MEMBER_ID FROM FORUM_MEMBERS WHERE MEMBER_ID= inserted.CL_ID)
BEGIN
INSERT INTO FORUM_MEMBERS (MEMBER_ID, M_NAME, M_USERNAME, M_PASSWORD, M_EMAIL,
M_DATE)
SELECT Cl_Id, Cl_Usuario, Cl_Nombre + ' ' + Cl_Apellido, Cl_Clave, Cl_Email_1, GetDate() FROM inserted
END
doesn't work. It seems that something is wrong here: "SELECT MEMBER_ID FROM FORUM_MEMBERS WHERE MEMBER_ID= inserted.CL_ID" where the "inserted" column prefix is not recognized. How should I do ? The basic idea is when there is a column change (so far I am not testing to which value but I will) in the table tblCliente I want to check if Cl_Id exists in the table FORUM_MEMBERS and in the case it does not exist add the corresponding row.
Jean-Luc
jeanluc@corobori.com
www.corobori.com
Jean-Luc
www.corobori.com
September 13, 2002 at 1:54 pm
Try this:
CREATE TRIGGER Update_Foro_Member
ON [dbo].[tblCliente]
AFTER UPDATE
AS
IF NOT UPDATE (Cl_Estado)
BEGIN
RETURN
END
IF NOT EXISTS (SELECT F.MEMBER_ID
FROM FORUM_MEMBERS F
JOIN inserted I
ON F.MEMBER_ID = I.CL_ID)
BEGIN
INSERT INTO FORUM_MEMBERS
(MEMBER_ID, M_NAME, M_USERNAME, M_PASSWORD, M_EMAIL, M_DATE)
SELECT
Cl_Id
, Cl_Usuario
, Cl_Nombre + ' ' + Cl_Apellido
, Cl_Clave
, Cl_Email_1
, GetDate()
FROM inserted
END
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
September 14, 2002 at 8:17 am
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply