Oracle Trigger – Can a Trigger Catch Updates Done by SELECT FOR UPDATE + LOB.WRITE()?

I'm not sure you need instead of triggers here.

If you are on Oracle 11g release 1 or later, create a virtual column for your concatenated compound key in the original table. Then create a unique index on the virtual column. You ought to be able to use this virtual column as a unique key.

Currently, your trigger is updating all rows. Modify it to use the inserted virtual table, something like this:

ALTER TRIGGER [dbo].[TRIG_Update_Inventory_Ind_Trees_Abs_Plot_Numb]
ON [dbo].[Plot_Location]
AFTER INSERT
AS 
BEGIN
    UPDATE Inventory_Ind_Trees
    SET Inventory_Ind_Trees.Abs_Plot_Numb = Plot_Location.Abs_Plot_Numb
    FROM Plot_Location
    WHERE EXISTS (
            SELECT 1 
            FROM inserted i 
            WHERE i.Invent_Plot_ID = Plot_Location.Invent_Plot_ID
        )
        AND Plot_Location.Invent_Plot_ID = Inventory_Ind_Trees.Invent_Plot_ID;
END

This may not be exactly correct; it's hard to tell exactly where to join the inserted virtual table with your other tables. Essentially, the idea is the inserted virtual table only contains rows that were either inserted or updated in the Plot_Location table.

You shouldn't check the @@ROWCOUNT system function at the start. It may not reflect the rowcount you think it does. Instead you should select the count of rows in the inserted table. Like this:

DECLARE @rc INT;
SELECT @rc = COUNT(1)
FROM inserted;

IF @rc > 0
BEGIN
    --body of trigger  goes here.
END