0

I have a table with an identity column (say Column1) and an integer column Column2 (nullable)

While inserting a row into this table, if the value passed to the Column2 is null, copy the Column1 value. If not null value is passed as argument for column2, retain the given value.

Only after the every insert, this check and update should be done.

But as per the instruction given to me, should not use default constraint and trigger (after insert).

This is for audit purpose. The record from this table will be moved/switched from one table to another table. The volume and transaction is very high (billions of records).

Can you help me on this?

Thanks, Baskaran

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Baskaran B
  • 1
  • 2
  • 1
    Which RDBMS is this for? Please add a tag to specify whether you're using `mysql`, `postgresql`, `sql-server`, `oracle` or `db2` - or something else entirely. Triggers are **highly vendor-specific** – marc_s Nov 30 '15 at 10:16

2 Answers2

0

Insert into tablename (column1, columns2) Values (value1, ISNULL(Value2,value1))

I think this what you are expecting.. No trigger just on insert

CMadhu
  • 346
  • 1
  • 8
  • The insert statement is coming from client machine. Sometimes client will provide value for column2. sometimes will not give. We don’t have option to modify the insert script as of now. Whenever the data inserted into the table, we are going to provide an unique ID for audit. Like where the record is moving across tables. We are planning to pass this unique ID on the hidden and non editable existing column. Is there any other option available. Thanks for your suggestion. – Baskaran B Nov 30 '15 at 11:30
  • @BaskaranB: if you cannot ensure no `NULL` is inserted, and you cannot modify the `INSERT` statements, then the pretty much only remaining solution to your problem is a **trigger** ... – marc_s Nov 30 '15 at 13:03
0

If you really want to do this in a trigger, try something like this:

CREATE TRIGGER TrgAfterINsert
ON dbo.YourTable
AFTER INSERT
AS
    UPDATE t
    SET Column2 = i.Column1
    FROM dbo.YourTable t
    INNER JOIN Inserted i ON i.Column1 = t.Column1
    WHERE t.Column2 IS NULL

This basically updates the YourTable table after an INSERT, and any rows that were inserted with a NULL in column2 are updated so that column2 is equal to the column1 value for that row

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • Transaction is very high and my team doesn’t want trigger due to performance considerations. – Baskaran B Nov 30 '15 at 11:33
  • @BaskaranB: well, in that case, just make sure no one ever inserts a `NULL` into `column2` ...... – marc_s Nov 30 '15 at 11:42
  • @BaskaranB: as long as you don't do any stupid things in your trigger (like using cursors, doing extensive processing), triggers can be in fact quite fast! Try it! – marc_s Nov 30 '15 at 17:15