0

I have a table and I fill one of the columns with a trigger if it is null or empty. I want to delete the trigger and do its job in code.

Do I have to first insert and after update or is there a better way? In .NET Framework, ORM is NHibernate

CREATE TABLE [dbo].[Table]
(
    [Id] INT NOT NULL PRIMARY KEY,
    [Col1] NVARCHAR(50) NOT NULL,
    [Col2] NVARCHAR(50) NOT NULL,
    [Code] NVARCHAR(100) NULL
);

CREATE TRIGGER Update_Table
ON [dbo].[Table]
AFTER INSERT
AS
BEGIN
    DECLARE @id INT
    
    SELECT @id = Id
    FROM inserted

    UPDATE [dbo].[Table]
    SET Code = 'CODE' + Id
    FROM [dbo].[Table]
    WHERE Id = @id AND Code IS NULL
END

I did this

Table entity = new Table() { Col1 = "aaa", Col2 = "bbb" };
entity = _repo.insert(entity);
entity.Code = "CODE" + entity.Id;
_repo.Update(entity);

sometimes i do not need update. Because users send this column value.

Table entity = new Table() { Col1 = "aaa", Col2 = "bbb", Code = "ccc" };
entity = _repo.insert(entity);

I tried insert then update. It is OK. Just seeking a better way.

eray
  • 13
  • 5

2 Answers2

0

I would simplify it by making CODE computed column, like this

CREATE TABLE [dbo].[Table]
(
    [Id] INT NOT NULL PRIMARY KEY,
    [Col1] NVARCHAR(50) NOT NULL,
    [Col2] NVARCHAR(50) NOT NULL,
    [Code] AS 'Code' + CAST(Id as NVARCHAR)
)

so, when inserting data, Code will be populated automatically

Nino
  • 6,931
  • 2
  • 27
  • 42
  • sorry for updating question after your reply. I can not update any column in the table. thank you. – eray Oct 27 '22 at 12:46
0

Notwithstanding Nino's answer, an interceptor is common way to achieve this.

Update:

It appears that event listeners are also an applicable technique too: https://stackoverflow.com/a/867356/1162077

You don't say how you're generating the entity id when it's not supplied by, so the event you intercept/handle will depend on how you're doing that.

David Osborne
  • 6,436
  • 1
  • 21
  • 35