3 Replies - 1961 Views - Last Post: 14 March 2013 - 09:37 AM

#1 jendrick  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 12
  • Joined: 07-March 13

Trigger Update

Posted 13 March 2013 - 01:35 AM

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


Is This A Good Question/Topic? 0
  • +

Replies To: Trigger Update

#2 mojo666  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 352
  • View blog
  • Posts: 770
  • Joined: 27-June 09

Re: Trigger Update

Posted 13 March 2013 - 10:35 AM

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.
Was This Post Helpful? 0
  • +
  • -

#3 jendrick  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 12
  • Joined: 07-March 13

Re: Trigger Update

Posted 13 March 2013 - 05:50 PM

Hi,

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

can you help me about this?
Was This Post Helpful? 0
  • +
  • -

#4 mojo666  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 352
  • View blog
  • Posts: 770
  • Joined: 27-June 09

Re: Trigger Update

Posted 14 March 2013 - 09:37 AM

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

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1