xeroxcanada
asked on
Update table after an Insert
I am looking for a trigger that will update my table after an Insert.
Table to run trigger on "TEMP"
When a record is entered I want to take the Field "MTSS_NAME" and go to the table called "ANALYST"
Analyst Table Contains the Fields "EMAIL" and "ManagerName"
Find all of the Email with the "ManagerName = to Mtss_Name" and create a string separated with a ";" and update the table "TEMP" with that string into the field "DistrictDL"
Can someone help Please.
Table to run trigger on "TEMP"
When a record is entered I want to take the Field "MTSS_NAME" and go to the table called "ANALYST"
Analyst Table Contains the Fields "EMAIL" and "ManagerName"
Find all of the Email with the "ManagerName = to Mtss_Name" and create a string separated with a ";" and update the table "TEMP" with that string into the field "DistrictDL"
Can someone help Please.
ASKER
Sorry but I am really green here.
I need to update the record as it is entered into the database.
The emails are inserted once.
I need to update the record as it is entered into the database.
The emails are inserted once.
If it really must be done, I would use a BEFORE INSERT trigger in DB2 to set the value of the column before it gets written to the table. I'm not sure of a good reason for doing it as an update after it's written.
I don't have appropriate experience with SQL Server to provide a reliable example.
Tom
I don't have appropriate experience with SQL Server to provide a reliable example.
Tom
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I realize you are have stated you are "really green here", however, you should know that TRIGGERs fire once per statement and not once per row. Clearly the way you have it coded does not apply when more than one row is INSERTed.
ASKER
I Need to understand this a bit more.
I have a form that does an Insert into the DB and inserts a new row.
So when that happens the trigger fires.
So if the form is sent twice lets say at the same time will the trigger fire twice or once?
The chance of someone filling in the form at the exact same time is very minimal.
I only get about 30 inserts in an 8-12 hour period.
So what am I missing?
I have a form that does an Insert into the DB and inserts a new row.
So when that happens the trigger fires.
So if the form is sent twice lets say at the same time will the trigger fire twice or once?
The chance of someone filling in the form at the exact same time is very minimal.
I only get about 30 inserts in an 8-12 hour period.
So what am I missing?
So what am I missing?
If your INSERT statement inserts more than one row, the TRIGGER will fire only once and in consequence @p1 and @p2 will contain the values from one row, even though INSERTED contains more than one row. If this will never happen (a single user INSERTs more than one row at one time) than you should be able to sleep fine.
If your INSERT statement inserts more than one row, the TRIGGER will fire only once and in consequence @p1 and @p2 will contain the values from one row, even though INSERTED contains more than one row. If this will never happen (a single user INSERTs more than one row at one time) than you should be able to sleep fine.
ASKER
zzzzzzzzzzzzzzzzzzzzzzzzzz
ASKER
Figured it out
With respect, that is not a good idea. For example, while this can be done to get the value correct at the INSERT time, what happens if later on the emails on the analyst table change?
You are better off to instead use a select query at the time you need the data. This is rather easily done using FOR XML PATH, assuming you are on SQL 2005 or later.
Patrick