asked
Syntax assistance
Please could someone look at the syntax below. Instead of just editing the ucPROID field from say Male to Female, it creates a new record in the table with the updated values. CREATE TRIGGER PROSPECTS ON _rtblProspect FOR INSERT, UPDATE AS BEGIN IF NOT EXISTS (SELECT cCompanyName FROM _rtblProspect WHERE cCompanyName NOT IN (SELECT cCompanyName FROM __PROSPECT_INFO)) BEGIN INSERT INTO __PROSPECT_INFO (cCompanyName,ucPROID) SELECT cCompanyName,ucPROID FROM INSERTED END ELSE BEGIN UPDATE __PROSPECT_INFO SET cCompanyName = (SELECT cCompanyName FROM _rtblProspect),ucPROID = (SELECT ucPROID FROM _rtblProspect) END END
ASK SQL Server Central runs by you voting. For all helpful answers below, please indicate this by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate this by clicking on the check mark next that answer. You can also mark your own answer.
It looks like if you modify the cCompanyName, it's going to insert data. Also, if you add a new company to _rtblProspect (scary table name) it's also going to insert data. Both scenarios will end up with inserted data.
You're referencing two different tables _rtblProspect and rtblProspect. If there are differences between these two, it could explain the issue.
The prospect info table is one that I created to simply insert or update the info from the _rtbl table. When an action occurs in the software, it deletes the info from the _rtbl table with all customisations attached to it. I am simply trying to store this in the prospect table till update time for reporting purposes.
OK, but if there are existing differences between these tables as the data is manipulated, you're going to see inserts as you have it defined.
I managed to find a working script. Here it is for those who are interested. CREATE TRIGGER [dbo].[PROSPECTS] ON [dbo].[_rtblProspect] AFTER INSERT, UPDATE AS BEGIN ; MERGE [dbo].[__PROSPECT_INFO] AS T USING [dbo].[_rtblProspect] AS S ON (T.[ProspectID] = S.[IDProspect]) WHEN NOT MATCHED BY TARGET THEN INSERT([ProspectID],[cCompanyName],[ucPROID]) VALUES([IDProspect],[cCompanyName],[ucPROID]) WHEN MATCHED THEN UPDATE SET T.[ucPROID] = S.[ucPROID] OUTPUT $action, inserted.*; end
That makes more sense. It's basically doing what you had above, but instead of matching on the name, it's using an ID. Those are unlikely to change every time.
15 People are following this question.
