Why do you expect B to be updated, when you inserted a row with a different id? Actually, your INSERT ALL
does not insert anything into B
.
The above works fine after fixing your trigger and sample data:
create or replace trigger updB
after update on B
for each row
declare
v varchar2(5);
begin
v := :new.val;
end;
/
This is how you reference the new val in the trigger, not with a select. Your original trigger would run into the infamous ORA-04091
"table is mutating" error.
SQL> insert all into A values ('a', 'false') into b values ('a', 'false') select * from dual;
2 rows created.
SQL> commit;
Commit complete.
SQL> update A set val = 'true';
Cek cek: false
1 row updated.
SQL> commit;
Commit complete.
SQL> select * from a;
ID VAL
---------- ----------
a true
SQL> select * from b;
ID VAL
---------- ----------
a true
For the output:
Your updA
trigger runs in an autonomous transaction. When you select val from A
, your original update is still not finished (commited), you will see the old value. Fixing your trigger for A
:
create or replace trigger updA
after update on A
for each row
declare
pragma autonomous_transaction;
cek varchar2(5);
begin
commit;
cek := :new.val;
dbms_output.put_line('Cek cek: ' || cek);
update B set val = :new.val where id = :new.id;
commit;
end;
/
The reason it's not working is that your join causes each row to be updated multiple times, but each update sees only the original values, not the cumulative values of previous updates. So your updates are in effect cancelling the previous updates.
(Or maybe SQL only allows one row update per statement. Either way the result is the same, with different resource use.)
Here's a quick and poorly formatted repro:
declare @insertedexample table (id INT, codeid int, value varchar(10))
INSERT INTO @insertedexample VALUES
(1,1,'newvalue1'),
(1,2,'newvalue2')
declare @targetexample table (id INT, codeid1value varchar(10), codeid2value varchar(10))
INSERT INTO @targetexample VALUES
(1, 'origvalue1', 'origvalue2')
SELECT * FROM @targetexample
UPDATE t
SET
t.codeid1value = CASE WHEN i.codeid=1 THEN i.value ELSE t.codeid1value END,
t.codeid2value = CASE WHEN i.codeid=2 THEN i.value ELSE t.codeid2value END
FROM @targetexample t
JOIN @insertedexample i on i.id = t.id
SELECT * FROM @targetexample
The only way I can think of at the moment to handle this is by building a dynamic query.
Aaron's suggestion of a view would certainly be better, provided users will only be filtering by fuelTransactions.id or the data will remain small enough to handle filtering after pivoting. (Edit: I later realised that it might also be possible to create a view, or a set of views, or certainly a function, that would allow efficient additional filtering, dependent of course on supporting indexing in the base table.)
UPDATE:
Actually you probably don't need a dynamic query, since the columns in the fuelTransactions table are fixed as are the efsCode codes you're interested in.
You might be able to use a pivot of one form or another, which incorporates the current values for the columns not being updated, even if you have to use a bunch of ugly subqueries.
I might have time to experiment with it, but can't promise.
Best Answer
Without more insight into the intended goal of this request, it certainly seems as though even with this immediate issue solved, the working code might not provide anything truly useful. Some concerns are:
UpdatedDate
or some date field in the table? If not, there is no sense of chronology without looking at the table creation date.UPDATE
, then you need to capture bothinserted
anddeleted
tables. But if they have GUID-based names, then you won't be able to correlate between the "inserted" and "deleted" copy tables for a particular UPDATE operation. You would have to re-use the same GUID value and denote the "insert" or "delete" in the table name prefix. If you weren't dynamically creating the table, then you could include a column specifying the DML action, dump bothinserted
anddeleted
tables into the already existing table, and just use a GUID or INT from a Sequence to correlate between 2 rows of the sameUPDATE
operation.However, all of that being said, the issue of interacting with the
inserted
anddeleted
tables via Dynamic SQL is an interesting problem. Unfortunately, it can't be done in T-SQL. So now it's also a challenge :-). Fortunately, this can actually be done. How so? With a little help from our friend, Mr SQLCLR.Now, there doesn't seem to be a lot of situations that really demand, or even benefit from, SQLCLR Triggers. They seem to be the least useful things you can create with SQLCLR. However, here we have a scenario that they are a wonderful fit for. SQL submitted from SQLCLR code is Dynamic SQL. And SQLCLR Triggers have access to the
inserted
anddeleted
tables, so it would seem that SQLCLR Triggers can access theinserted
anddeleted
tables in Dynamic SQL. Below is the code that does exactly that to accomplish this request (please note that the DB connection is using the in-process "Context Connection", so the Assembly can be marked withPERMISSION_SET = SAFE
; no need for an Asymmetric Key or to set the database toTRUSTWORTHY ON
):Test table for the Trigger to be created on (if using Visual Studio / SSDT, the table definition has to be included in the project):
The SQLCLR C# code:
T-SQL wrapper object to place the SQLCLR Trigger onto the Table: