CREATE TABLE [dbo].[al_paydet]
(
[pdduedt] [datetime] NULL,
[pddueamt] [float] NULL,
[pdpaydt] [datetime] NULL,
[pdpayamt] [float] NULL,
[pdintref] [nvarchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[pdpayref] [nvarchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[cost_center] [int] NULL,
[pdcommnt] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[gl_code] [int] NULL,
[creation_user] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[creation_date] [int] NULL,
[last_update_user] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[last_update_date] [int] NULL,
[version_number] [int] NULL CONSTRAINT [DF__AL_PAYDE__VERSI__540C7B00] DEFAULT ('0  '),
[payment_id] [bigint] NOT NULL,
[cost_id] [bigint] NOT NULL,
[tenant] [binary] (16) NULL
) ON [PRIMARY]

GO
CREATE TRIGGER dbo.ca_tr_del_al_paydet
ON dbo.al_paydet
FOR DELETE AS
DECLARE
        @audit_product nvarchar(64)
        SET @audit_product = APP_NAME()
INSERT INTO dbo.aud_al_paydet (
AUDIT_TRAIL_PRODUCT,
AUDIT_TRAIL_USER,
AUDIT_TRAIL_TYPE,
AUDIT_TRAIL_DATE,
pdduedt,
pddueamt,
pdpaydt,
pdpayamt,
pdintref,
pdpayref,
cost_center,
pdcommnt,
gl_code,
creation_user,
creation_date,
last_update_user,
last_update_date,
version_number,
payment_id,
cost_id,
tenant) SELECT

@audit_product,old.last_update_user, 'DELETE', datediff(ss, '1/1/1970', getutcdate()),
old.pdduedt,
old.pddueamt,
old.pdpaydt,
old.pdpayamt,
old.pdintref,
old.pdpayref,
old.cost_center,
old.pdcommnt,
old.gl_code,
old.creation_user,
old.creation_date,
old.last_update_user,
old.last_update_date,
old.version_number,
old.payment_id,
old.cost_id,
old.tenant
FROM deleted old
GO
CREATE TRIGGER dbo.ca_tr_ins_al_paydet
ON dbo.al_paydet
FOR INSERT AS
DECLARE
        @audit_product nvarchar(64)
        SET @audit_product = APP_NAME()
INSERT INTO dbo.aud_al_paydet (
AUDIT_TRAIL_PRODUCT,
AUDIT_TRAIL_USER,
AUDIT_TRAIL_TYPE,
AUDIT_TRAIL_DATE,
pdduedt,
pddueamt,
pdpaydt,
pdpayamt,
pdintref,
pdpayref,
cost_center,
pdcommnt,
gl_code,
creation_user,
creation_date,
last_update_user,
last_update_date,
version_number,
payment_id,
cost_id,
tenant) SELECT

@audit_product, new.last_update_user, 'INSERT', datediff(ss, '1/1/1970', getutcdate()),
new.pdduedt,
new.pddueamt,
new.pdpaydt,
new.pdpayamt,
new.pdintref,
new.pdpayref,
new.cost_center,
new.pdcommnt,
new.gl_code,
new.creation_user,
new.creation_date,
new.last_update_user,
new.last_update_date,
new.version_number,
new.payment_id,
new.cost_id,
new.tenant
FROM inserted new
GO
CREATE TRIGGER dbo.ca_tr_upd_al_paydet
ON dbo.al_paydet
FOR UPDATE AS
DECLARE
        @audit_product nvarchar(64),
        @last_update_user nvarchar(64),
        @version_number int


        SET @audit_product = APP_NAME()
SELECT  @version_number = version_number, @last_update_user = last_update_user FROM inserted

IF @version_number != -1
INSERT INTO dbo.aud_al_paydet (
AUDIT_TRAIL_PRODUCT,
AUDIT_TRAIL_USER,
AUDIT_TRAIL_TYPE,
AUDIT_TRAIL_DATE,
pdduedt,
pddueamt,
pdpaydt,
pdpayamt,
pdintref,
pdpayref,
cost_center,
pdcommnt,
gl_code,
creation_user,
creation_date,
last_update_user,
last_update_date,
version_number,
payment_id,
cost_id,
tenant) SELECT

@audit_product, new.last_update_user, 'UPDATE', datediff(ss, '1/1/1970', getutcdate()),
new.pdduedt,
new.pddueamt,
new.pdpaydt,
new.pdpayamt,
new.pdintref,
new.pdpayref,
new.cost_center,
new.pdcommnt,
new.gl_code,
new.creation_user,
new.creation_date,
new.last_update_user,
new.last_update_date,
new.version_number,
new.payment_id,
new.cost_id,
new.tenant
  FROM inserted new
GO
ALTER TABLE [dbo].[al_paydet] ADD CONSTRAINT [xpkal_paydet] PRIMARY KEY CLUSTERED ([payment_id]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [al_paydet_idx01] ON [dbo].[al_paydet] ([pdduedt]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[al_paydet] ADD CONSTRAINT [FK_AL_PAYDET_TENANT] FOREIGN KEY ([tenant]) REFERENCES [dbo].[ca_tenant] ([id])
GO
ALTER TABLE [dbo].[al_paydet] ADD CONSTRAINT [PDCDJOIN02] FOREIGN KEY ([cost_id]) REFERENCES [dbo].[al_costdet] ([cost_id])
GO
ALTER TABLE [dbo].[al_paydet] ADD CONSTRAINT [PDGLCJOIN02] FOREIGN KEY ([gl_code]) REFERENCES [dbo].[ca_resource_gl_code] ([id])
GO
ALTER TABLE [dbo].[al_paydet] ADD CONSTRAINT [PDRCCJOIN02] FOREIGN KEY ([cost_center]) REFERENCES [dbo].[ca_resource_cost_center] ([id])
GO