Update in an Insert Trigger

Question

Post reply

Update in an Insert Trigger

bswhipp

Ten Centuries

Points: 1125
More actions
May 19, 2023 at 12:45 pm

#4193026

I have a need to set current fields to default values when a new record is inserted. Is it good practice to update the same table in an after insert trigger?
June 1, 2023 at 3:53 pm

This was removed by the editor as SPAM

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 101192 More actions · Answer 1

Normally you'd just specify a default value rather than having to use a trigger for that.

For example, say you have table "tableA" that you want to add a default value of current date for "column4", then:

ALTER TABLE dbo.tableA ADD DEFAULT GETDATE() FOR column4;

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

bswhipp Ten Centuries Points: 1125 More actions · Answer 2

I would but the system that created the record updates the record based on the record it copied from (Add and Carry) instead of Add. So If I default a field, it will be updated any way. So on the back end after the insert, I need to change the value.

ScottPletcher SSC Guru Points: 101192 More actions · Answer 3

Which dbms are you using? SQL Server? Access? Oracle?

This is a SQL Server forum. Someone here might be able to help you with another dbms, but less so than with SQL Server.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

bswhipp Ten Centuries Points: 1125 More actions · Answer 4

SQL. The program is our ERP In the Item master users Do an "Add and Carry" What that does is it takes the current record and copies it to a new record with the same values as the old. So I need to default a few fields to something else after they save the record in the program as to not have bad data.

ScottPletcher SSC Guru Points: 101192 More actions · Answer 5

Cool, I can help more/fully with SQL Server:

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
CREATE TRIGGER dbo.trigger_name
ON dbo.table_name
AFTER INSERT
AS
UPDATE tn
SET col1=GETDATE(), col2=0, col3='' /*, ...*/
FROM dbo.table_name tn
INNER JOIN inserted i ON i.key_col = tn.key_col
GO

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Update in an Insert Trigger

Cookies on SQLServerCentral