Insert into 2nd Database if it doesn't exist

Arsi · November 19th, 2004, 12:49 PM

Hello,

I got some help with the update when I was working on that part, but now I need to add some more to the trigger and I've running into a lot of problems. Most of them I've fixed, but now I'm stuck. If anyone can help me I would be very thankful.

Here it is:

ALTER TRIGGER [Update_DB] ON [dbo].[ProjectInfo]
FOR INSERT, UPDATE
AS
If UPDATE(ProjectName)
IF EXISTS (SELECT * FROM INSERTED)
    UPDATE ProjectStatus..ProjectInfo2
    SET ProjectName = i.ProjectName
    FROM DELETED d, ProjectStatus..ProjectInfo2 t, INSERTED i
    WHERE d.ProjectName = t.ProjectName
ELSE
INSERT INTO ProjectStatus..ProjectInfo2
VALUES ('BidNum', 'ProjectName', 'ProjectLead', 'WklyTotalHrs', 'TotalHrs', 'EmpNum')

Ok. I'm trying to update a record in Table ProjectInfo2 in the ProjectStatus database if an update occurs to Table ProjectInfo in the dbTimeSheet database. Also, it has to check to see if the record exists (mainly the ProjectName) in Table ProjectInfo2 if it does then update, else insert. I am not receiving any errors, but when I there is an update, it does not do the insert if it does not exist.

'BidNum', 'ProjectName'...etc are actually column names in the ProjectInfo2 Table. I am so lost.

*******(*)*******

happygv · November 22nd, 2004, 06:45 PM

Can you post the structure of both the tables(on which the UPDATE happens AND onto which the UPDATE or INSERT has to happen)? That should clear some of the doubts I have in my mind.

Cheers!

_________________________
- Vijay G
Strive for Perfection

Arsi · November 22nd, 2004, 07:24 PM

Sorry I can be confusing even to myself. When db1; Table ProjectInfo gets updated or has a new record inserted, then it should trigger the second database to either insert the new record or update an existing one. The second database is ProjectStatus and has only one table ProjectInfo2. The second Database has only one table. The other is a Microsoft Sharepoint Portal database.

-Thanks Again!:)

*******(*)*******

happygv · November 22nd, 2004, 07:42 PM

Yes I understand that. But I would like to see the structure of both ProjectInfo and ProjectInfo2 tables. If they both are similar, you may use the INSERTED table's values as such to insert into the ProjectInfo2.

Code:

INSERT INTO ProjectStatus..ProjectInfo2 SELECT * from INSERTED

If the sequence of columns in PROJECTINFO2 are not the same as that of the PROJECTINFO, then you may sequence it within the SELECT statement given above.

Code:

INSERT INTO ProjectStatus..ProjectInfo2 SELECT COL1, COL2, COL3,... from INSERTED

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection

Arsi · November 23rd, 2004, 12:36 PM

OMG! I understand now! Thank you so very much! This is very exciting! I am now using the second part of the sample you gave me and its now functioning wonderfully! Thank you for all your help!:D

*******(*)*******

happygv · November 23rd, 2004, 03:06 PM

Thats great! Glad the you are through.

Cheers!:)

_________________________
- Vijay G
Strive for Perfection