Triggers

I just started playing around with Triggers and am not certain, whether I am
missing something or not.

It seems, that the UPDATE Trigger only kicks in, if the changed column is
"not null", but has a value already?

Kind Regards
Wolfgang

Here's the Code:

DROP TRIGGER IF EXISTS "DebtorLog";
DROP TABLE IF EXISTS "DebtorLog";
CREATE TABLE "DebtorLog"(
"Action" VARCHAR(15),
"Occurred" DATETIME,
"CurrentUser" VARCHAR(45),
"Account" VARCHAR(8),
"Category" VARCHAR(1),
"Contact" VARCHAR(25),
"CreditLimit" REAL
);
CREATE INDEX "AccountIx" ON "DebtorLog"("Account" IGNORE CASE);
CREATE INDEX "CategoryIx" ON "DebtorLog"("Category" IGNORE CASE);
CREATE INDEX "ContactIx" ON "DebtorLog"("Contact" IGNORE CASE);
CREATE TRIGGER "DebtorLog"
AFTER INSERT,DELETE,UPDATE ON "Debtor"
REFERENCING OLD AS o NEW AS n
BEGIN
IF INSERTING THEN
INSERT INTO "DebtorLog"("Action", "Occurred", "CurrentUser",
"Account",
"Category",
"Contact",
"CreditLimit")
VALUES('Insert',CURRENT_TIMESTAMP, CURRENT_USER,
n."Account",
n."Category",
n."Contact",
n."CreditLimit");
END IF;
IF DELETING THEN
INSERT INTO "DebtorLog"("Action", "Occurred", "CurrentUser",
"Account",
"Category",
"Contact",
"CreditLimit")
VALUES('Delete',CURRENT_TIMESTAMP, CURRENT_USER,
o."Account",
o."Category",
o."Contact",
o."CreditLimit");
END IF;
IF UPDATING
and (
(coalesce(o.Dormant, False) = False)
)
and ((o."Account" <> n."Account")
or (o."Category" <> n."Category")
or (o."Contact" <> n."Contact")
or (o."CreditLimit" <> n."CreditLimit"))
THEN
INSERT INTO "DebtorLog"("Action", "Occurred", "CurrentUser",
"Account",
"Category",
"Contact",
"CreditLimit")
VALUES('Before',CURRENT_TIMESTAMP, CURRENT_USER,
o."Account",
o."Category",
o."Contact",
o."CreditLimit"),
('After',CURRENT_TIMESTAMP, CURRENT_USER,
n."Account",
n."Category",
n."Contact",
n."CreditLimit");
END IF;
END

Wolfgang wrote:
> I just started playing around with Triggers and am not certain,
> whether I am missing something or not.
>
> It seems, that the UPDATE Trigger only kicks in, if the changed
> column is "not null", but has a value already?
>
> Kind Regards Wolfgang

Nulls won't play well with your conditions.
Use NOT EQUIVALENT(A,B) instead of A <> B.

--
Brian Evans [NDX]
Ottawa, ON, CANADA
GMT-5

Thanks Brian,

works perfectly now.

Kind Regards
Wolfgang

"Brian Evans [NDX]" <bevanson@rogers.com> wrote in message
news:4704f3ec$1@nexus-vkpb9bih5.Nexus.local...
> Wolfgang wrote:
>> I just started playing around with Triggers and am not certain,
>> whether I am missing something or not.
>>
>> It seems, that the UPDATE Trigger only kicks in, if the changed
>> column is "not null", but has a value already?
>>
>> Kind Regards Wolfgang
>
> Nulls won't play well with your conditions.
> Use NOT EQUIVALENT(A,B) instead of A <> B.
>
> --
> Brian Evans [NDX]
> Ottawa, ON, CANADA
> GMT-5