Tool's generated code/trigger

Anonymous

I've been trying to figure out the trigger code which is generated by the tool for MS SQL. The first thing that comes to mind: when would you ever have a null value for the anchor id? Second, table @PE is declared to be not null for all fields. However, the WHERE clause has "inserted.PE_ID is null" which doesn't jive

Third, I don't understand how you are matching up the row numbers on the join.

Here is a sample:

--------------------------------- [Insert Trigger] -----------------------------------

-- PE_Person insert trigger on the latest perspective

--------------------------------------------------------------------------------------

IF EXISTS (SELECT * FROM sys.triggers WHERE name = 'itPE_Person')

DROP TRIGGER [dbo].[itPE_Person]

CREATE TRIGGER [dbo].[itPE_Person] ON lPE_Person

INSTEAD OF INSERT

BEGIN

SET NOCOUNT ON;

DECLARE @now DATETIME2(7) = SYSDATETIME();

DECLARE @v INT, @maxV INT;

DECLARE @PE TABLE (

Row int identity(1,1) not null primary key,

PE_ID int not null

);

INSERT INTO [dbo].[PE_Person](

Metadata_PE

)

OUTPUT

inserted.PE_ID

INTO

@PE

SELECT

Metadata_PE

FROM

inserted

WHERE

inserted.PE_ID is null;

DECLARE @inserted TABLE (

PE_ID int not null,

Metadata_PE metatype not null,

PE_GEN_RecordedAt datetime null,

PE_GEN_ErasedAt datetime null,

Metadata_PE_GEN metatype null,

PE_GEN_Person_Gender string null

);

INSERT INTO @inserted

SELECT

ISNULL(i.PE_ID, a.PE_ID),

i.Metadata_PE,

ISNULL(i.PE_GEN_RecordedAt, @now),

ISNULL(i.PE_GEN_ErasedAt, '9999-12-31'),

ISNULL(i.Metadata_PE_GEN, i.Metadata_PE),

i.PE_GEN_Person_Gender

FROM (

SELECT

PE_ID,

Metadata_PE,

PE_GEN_RecordedAt,

PE_GEN_ErasedAt,

Metadata_PE_GEN,

PE_GEN_Person_Gender,

ROW_NUMBER() OVER (PARTITION BY PE_ID ORDER BY PE_ID) AS Row

FROM

inserted

) i

LEFT JOIN

@PE a

a.Row = i.Row;

INSERT INTO [dbo].[PE_GEN_Person_Gender](

PE_GEN_PE_ID,

PE_GEN_Person_Gender,

Metadata_PE_GEN,

PE_GEN_RecordedAt,

PE_GEN_ErasedAt

)

SELECT

i.PE_ID,

i.PE_GEN_Person_Gender,

i.Metadata_PE_GEN,

i.PE_GEN_RecordedAt,

i.PE_GEN_ErasedAt

FROM

@inserted i

WHERE

i.PE_GEN_Person_Gender is not null;

END

Roenbaeck

The trigger has two modes of operation, depending on if you have provided a PE_ID in the insert or not. Compare these two:

Unknown mode

insert into lPE_Person (PE_NAM_Person_Name)

values ('Jane Doe');

Known mode

insert into lPE_Person (PE_ID, PE_NAM_Person_Name)

values (42, 'Jane Doe');

In the "unknown mode" the trigger will create new identities, PE_ID, for you, which are stored in the @PE table. This is why there is a condition to check for which rows PE_ID is null in the inserted table. In the "known mode" the values you have provided for PE_ID will be used instead. The trigger can also handle mixed modes, if you have a source table that you are loading from in which only some PE_ID are null.

The @PE table has an automatically incremented column called Row. This will associate each created PE_ID with a row number, 1, 2, 3, 4, ..., and so on. When the @inserted table is populated, the windowed function ROW_NUMBER() is used to create a similar sequence 1, 2, 3, 4, ..., and so on for the cases where PE_ID is null, thanks to the partition by and order by clause. Actually, every explicitly specified PE_ID will get the value 1, but these are preserved thanks to the ISNULL(i.PE_ID, a.PE_ID) statement in the insert. For every row where PE_ID is null one of the newly generated PE_ID will be picked up instead.

Also note that the trigger you posted is not 'idempotent', in which case there would be even more logic to check if you are trying to insert "the same" value again. Sameness in the current version of the tool is checked only against the previous value. This is however going to be extended to also checking the following value in the next version, which is about to be released for testing very soon.

Anonymous

I'm still digesting what you wrote. However, I didn't want to delay in replying - thank you for both being very prompt and complete.

Anonymous

Ok. I think I've finally understand.

FYI, the triggers on Postgres don't present the entire changed set at once. Instead, the NEW and OLD keywords contain one changing record at a time. Thus, I *should* be able to handle the NULL PE_ID fairly easily.

However, this complicates ties. Again, looking at the generated code, it looks like ties are updated in the trigger in changedAt order. This seems impossible to handle in Postgres because only one record at a time is presented to the trigger function.

Assuming you did this to handle idempotent and restatement issues, it may be impossible to handle idempotent/restatement in Postgres. Ug.

Roenbaeck

The reason for the while-loop over versions (which is what I think you are referring to) is to prevent issues when you have multiple versions in a single batch that you load. In that case the trigger will start by loading the earliest version, and provided that it succeeds, the next version will be checked to see if it is a restatement or not, and so on...

Do you think restatement prevention and idempotency may be achievable if we limit loading to only contain one version at a time? I don't think that's a big loss, since it can be handled quite easily with some extra logic outside of the trigger.

Anonymous

Actually, yes I was referring the to while-loop -- excuse me for not being clear.

As far as restatement and idempotency, I haven't even looked at that yet. My "gut" would tell me limiting loading to one version at a time *may* make restatement and idempotency possible.

I guess you could prevent loadiing multiple versions at once by using a 'before' trigger in addition to the 'instead' trigger. The 'before' trigger could throw an error if items came in out of order, or more simply, throw an error if more than one item arrives. Unfortunately, I don't see a way to sort them.