CODEWITHSUNDEEP
c#asp.netsqltriggersinsert
Rale
Rale

Reputation: 43

Before Insert Trigger SQL

I have two identical tables, I_Subject and I_Temp_Subject and I want to copy the Temp_Subject table to the Subject table. I_Temp_Subject is used by Simple Users and I_Subject is used by the Admin. The Admin can insert new records directly to I_Subject and validates the content of I_Temp_Subject.

Both table IDs are set to auto increment. The copy SQL command is as follows:

INSERT INTO I_SUBJECT(ID,CATEGORY_ID,USER_NAME,SERIAL_NUMBER,ICON)
SELECT ID,CATEGORY_ID,USER_NAME,SERIAL_NUMBER,ICON FROM I_TEMP_SUBJECT

If I do that then the following error occurs:

System.Data.SqlClient.SqlException: Cannot insert explicit value for identity
column in table 'I_SUBJECT' when IDENTITY_INSERT is set to OFF.

If I don't include the ID in the above statement then everything is ok except the ID's are not identical.

So I want to include a before insert trigger to check if the insert has null ID then autoincrement (when the Admin inserts directly) else if the insert's ID !=null then copy the ID from the I_Temp_Subject (when the Admin validates).

Upvotes: 3

Views: 547

Answers (2)

RVD
RVD

Reputation: 123

you can turn off auto increament by using following statement as

SET IDENTITY_INSERT dbo.<tablename>  ON
 write your insert statement here

and then turn off identity insert as SET IDENTITY_INSERT dbo. OFF

i.e.

    SET IDENTITY_INSERT dbo.I_SUBJECT  ON
    INSERT INTO I_SUBJECT(ID,CATEGORY_ID,USER_NAME,SERIAL_NUMBER,ICON)
    SELECT ID,CATEGORY_ID,USER_NAME,SERIAL_NUMBER,ICON FROM I_TEMP_SUBJECT
    SET IDENTITY_INSERT dbo.I_SUBJECT OFF

Upvotes: 1

Corin Maslin
Corin Maslin

Reputation: 41

Since the backup table is to be a direct copy, I'd turn off the autoincrement on that one so that the two will always be in sync. If that't not the option, turn on identity insert, though I think it'd be unnecessary.

SET IDENTITY_INSERT dbo.<tablename> ON
<your insert statements>
SET IDENTITY_INSERT dbo.<tablename> OFF

Upvotes: 2

Related Questions