[jendrick]

Can someone check my code, it gives me an error msg.

ERROR MESSAGE:
Msg 4104, Level 16, State 1, Procedure TRG_Guestshistory, Line 10
The multi-part identifier "maintable.fname" could not be bound.
Msg 4104, Level 16, State 1, Procedure TRG_Guestshistory, Line 10
The multi-part identifier "maintable.lname" could not be bound.

ALTER TRIGGER [dbo].[Tgr_guests]
   ON  [dbo].[maintable]
   AFTER UPDATE
AS 


IF (NOT EXISTS(SELECT * FROM guesthistory WHERE fname = maintable.fname AND lastname = maintable.lname) )

BEGIN
	


INSERT INTO guesthistory

(salutation,fname,mname,lastname,nationality,address,eadd,phoneno,mobileno,discard,company,birthdate)

Select sal,fname,mname,lname,nationality,address,eadd,phoneno,mobileno,discard,company,birthdate

from INSERTED	
   
	
	
	

END

[mojo666]

IF (NOT EXISTS(SELECT * FROM guesthistory WHERE fname = maintable.fname AND lastname = maintable.lname) )

You can't just compare columns in guesthistory to fields in other tables. You need to write a join statement including that table. I am not sure what the requirements are, but I think you want to be joining guesthistory to INSERTED and not maintable.

[jendrick]

Hi,

i'm trying to avoid duplicate or same firstname and lastname in guesthistory table.

can you help me about this?

[mojo666]

My prefered method instead of the if statement would be

INSERT INTO GuestHistory
(/*columns*/)
SELECT
(i.salutation, i.fname, i.lname, /*rest of columns*/)
FROM INSERTED i
LEFT JOIN
GuestHistory gh
ON gh.fname=i.fname AND gh.lname=i.lname
WHERE gh.fname IS NULL

The join creates a result set that has all the fields of INSERTED and GuestHistory. Since it is a LEFT join, all the data rows in INSERTED are included. Data from guest history is only included if it has a match to INSERTED. The WHERE clause filters out entries that have a match in Guest History so all that remains are guests that are not in the history. Here's an example of how the data might look.

//Guest History
John	Doe
Jane	Doe
John	Smith

//INSERTED
John	Doe
Jane	Smith

//INSERTED LEFT JOIN GuestHistory
John	Doe	John	Doe
Jane	Smith	NULL	NULL

//WHERE gh.fname IS NULL
Jane	Smith	NULL	NULL

This post has been edited by mojo666: 14 March 2013 - 09:38 AM