I've created my own system table and I thought I set everything up correctly compared to the directions in the system table documentation. But when I add a new row to my new table, once I save it none of the audit columns are being generated with data like they would in any other system table.
I created the table, ran the UP_POPULATE_REFERENCE_METADATA procedure, built each column in the TR_REFERENCE_COLUMN table and made sure to assign the right column types to each of the audit columns using the existing tables setup as a reference. Was there a step I missed somewhere or did I just not set the table up properly?
Hi Jesse,
I find that I have to explicitly create my constraints and triggers for systems tables, such as:
ALTER TABLE [dbo].[LTR_CPSMA_TNEW_FEE_ALIAS] ADD CONSTRAINT [DF_LTR_CPSMA_TNEW_FEE_ALIAS_create_dt] DEFAULT (getdate()) FOR [create_dt] GO ALTER TABLE [dbo].[LTR_CPSMA_TNEW_FEE_ALIAS] ADD CONSTRAINT [DF_LTR_CPSMA_TNEW_FEE_ALIAS_create_loc] DEFAULT ([dbo].[fs_location]()) FOR [create_loc] GO ALTER TABLE [dbo].[LTR_CPSMA_TNEW_FEE_ALIAS] ADD CONSTRAINT [DF_LTR_CPSMA_TNEW_FEE_ALIAS_created_by] DEFAULT ([dbo].[fs_user]()) FOR [created_by] GO ALTER TABLE [dbo].[LTR_CPSMA_TNEW_FEE_ALIAS] ADD CONSTRAINT [DF_LTR_CPSMA_TNEW_FEE_ALIAS_last_update_dt] DEFAULT (getdate()) FOR [last_update_dt] GO ALTER TABLE [dbo].[LTR_CPSMA_TNEW_FEE_ALIAS] ADD CONSTRAINT [DF_LTR_CPSMA_TNEW_FEE_ALIAS_last_updated_by] DEFAULT ([dbo].[fs_user]()) FOR [last_updated_by] GO IF OBJECT_ID ('TG_LTR_CPSMA_TNEW_FEE_ALIAS_UPDATE', 'TR') IS NOT NULL DROP TRIGGER [dbo].[TG_LTR_CPSMA_TNEW_FEE_ALIAS_UPDATE] GO CREATE TRIGGER [dbo].[TG_LTR_CPSMA_TNEW_FEE_ALIAS_UPDATE] ON [dbo].[LTR_CPSMA_TNEW_FEE_ALIAS] FOR UPDATE AS BEGIN --update last_updated_by/date UPDATE a SET last_updated_by = [dbo].FS_USER(), last_update_dt = getdate() FROM [dbo].LTR_CPSMA_TNEW_FEE_ALIAS a JOIN inserted b ON a.id = b.id END GO
I add these before running UP_POPULATE_REFERENCE_METADATA.
--Gawain
That is exactly what needed to be done. Thanks for the help