Sql trigger for updating one table from another

I am looking to copy the primary email address into the PERSON table anytime it is created or updated.

The following structure describes the PERSON table and EMAIL table As you can see, the two tables are linked on the PERSONID column.

')) FOR [APP_NAME]; ALTER TABLE [AUDIT].[LOG_TABLE_CHANGES] ADD CONSTRAINT [DF_LTC_HOST_NAME] DEFAULT (coalesce(host_name(),'?

')) FOR [HOST_NAME]; GO -- -- 8 - Make DML trigger to capture changes -- -- Delete existing trigger IF OBJECT_ID('[ACTIVE].[TRG_FLUID_DATA]') IS NOT NULL DROP TRIGGER [ACTIVE].[TRG_FLUID_DATA] GO -- Add trigger to log all changes CREATE TRIGGER [ACTIVE].[TRG_FLUID_DATA] ON [ACTIVE].[CARS_BY_COUNTRY] FOR INSERT, UPDATE, DELETE AS BEGIN -- Detect inserts IF EXISTS (select * from inserted) AND NOT EXISTS (select * from deleted) BEGIN INSERT [AUDIT].[LOG_TABLE_CHANGES] ([CHG_TYPE], [SCHEMA_NAME], [OBJECT_NAME], [XML_RECSET]) SELECT 'INSERT', '[ACTIVE]', '[CARS_BY_COUNTRY]', (SELECT * FROM inserted as Record for xml auto, elements , root('Record Set'), type) RETURN; END -- Detect deletes IF EXISTS (select * from deleted) AND NOT EXISTS (select * from inserted) BEGIN INSERT [AUDIT].[LOG_TABLE_CHANGES] ([CHG_TYPE], [SCHEMA_NAME], [OBJECT_NAME], [XML_RECSET]) SELECT 'DELETE', '[ACTIVE]', '[CARS_BY_COUNTRY]', (SELECT * FROM deleted as Record for xml auto, elements , root('Record Set'), type) RETURN; END -- Update inserts IF EXISTS (select * from inserted) AND EXISTS (select * from deleted) BEGIN INSERT [AUDIT].[LOG_TABLE_CHANGES] ([CHG_TYPE], [SCHEMA_NAME], [OBJECT_NAME], [XML_RECSET]) SELECT 'UPDATE', '[ACTIVE]', '[CARS_BY_COUNTRY]', (SELECT * FROM deleted as Record for xml auto, elements , root('Record Set'), type) RETURN; END END; GO If you are having a-lot of changes to the table, then either purge data on a cycle or just record the modified date in another table like you stated. Nice thing about my solution is that it tells you when and who did the change.

However, I'm stumped on how to CREATE TRIGGER Prices Update Trigger ON Prices AFTER INSERT, UPDATE, DELETE AS DECLARE @Update Type nvarchar(1) DECLARE @Updated DT datetime SELECT @Updated DT = CURRENT_TIMESTAMP IF EXISTS (SELECT * FROM inserted) IF EXISTS (SELECT * FROM deleted) SELECT @Update Type = 'U' -- Update Trigger ELSE SELECT @Update Type = 'I' -- Insert Trigger ELSE IF EXISTS (SELECT * FROM deleted) SELECT @Update Type = 'D' -- Delete Trigger ELSE SELECT @Update Type = NULL; -- Unknown Operation IF @Update Type = 'I' BEGIN -- Log an insertion record END IF @Update Type = 'U' BEGIN -- Log an update record END IF @Update Type = 'D' BEGIN -- Log a deletion record END GO -- -- 7 - Auditing data changes (table for DML trigger) -- -- Delete existing table IF OBJECT_ID('[AUDIT].[LOG_TABLE_CHANGES]') IS NOT NULL DROP TABLE [AUDIT].[LOG_TABLE_CHANGES] GO -- Add the table CREATE TABLE [AUDIT].[LOG_TABLE_CHANGES] ( [CHG_ID] [numeric](18, 0) IDENTITY(1,1) NOT NULL, [CHG_DATE] [datetime] NOT NULL, [CHG_TYPE] [varchar](20) NOT NULL, [CHG_BY] [nvarchar](256) NOT NULL, [APP_NAME] [nvarchar](128) NOT NULL, [HOST_NAME] [nvarchar](128) NOT NULL, [SCHEMA_NAME] [sysname] NOT NULL, [OBJECT_NAME] [sysname] NOT NULL, [XML_RECSET] [xml] NULL, CONSTRAINT [PK_LTC_CHG_ID] PRIMARY KEY CLUSTERED ([CHG_ID] ASC) ) ON [PRIMARY] GO -- Add defaults for key information ALTER TABLE [AUDIT].[LOG_TABLE_CHANGES] ADD CONSTRAINT [DF_LTC_CHG_DATE] DEFAULT (getdate()) FOR [CHG_DATE]; ALTER TABLE [AUDIT].[LOG_TABLE_CHANGES] ADD CONSTRAINT [DF_LTC_CHG_TYPE] DEFAULT ('') FOR [CHG_TYPE]; ALTER TABLE [AUDIT].[LOG_TABLE_CHANGES] ADD CONSTRAINT [DF_LTC_CHG_BY] DEFAULT (coalesce(suser_sname(),'?

')) FOR [CHG_BY]; ALTER TABLE [AUDIT].[LOG_TABLE_CHANGES] ADD CONSTRAINT [DF_LTC_APP_NAME] DEFAULT (coalesce(app_name(),'?

I need that to eliminate double manual insert/update.

lets say this is very simple version of this trigger: CREATE TRIGGER auto_list AFTER UPDATE ON trig1 FOR EACH ROW BEGIN UPDATE row1 SET R=WHERE id=NEW.

sql trigger for updating one table from another-15sql trigger for updating one table from another-42

This works fine when I try to update all the records in tbl A, however, in this case I only have missing data which I have identified and populated in tbl B., which contains tens of thousands of rows of data.This table is used heavily by legacy applications and unfortunately cannot be modified (no columns can be added, removed, or modified).This is what I have been able to get so far in SQL based on my limited understanding of triggers, in case it helps (I realise it's not very good, treat it as pseudocode): good enough start.first - let's get this out of the way - this is not well normalized - the value you describe seems like it should be calculated at runtime not at data manipulation time.

Leave a Reply