February 23, 2012 at 2:53 am
Hi,
can anyone help with a code please.
i need to create a trigger on a table 1 inserts (after insert)
that will isert records to table 2 only when the inserts of table 1 are with the following logic:
SELECT *
FROM table 1
where Tenant_ID = 1
and State_ID = 1 or State_ID = 7
Thank you
Ofer
February 23, 2012 at 3:00 am
so you only want the trigger to insert into table 2 where a row has been inserted into table 1 where the Tenant_ID is 1
and State_ID = 1 or State_ID = 7
CREATE TRIGGER Trig1 ON table1
AFTER INSERT
AS
INSERT INTO table2 (column list here)
SELECT
columns
FROM
inserted
WHERE
Tenant_ID = 1
AND
State_ID IN (1,7)
February 23, 2012 at 4:28 am
Thank you ,
but it seems that now i have trouble with 'timestamp' columns:
Msg 273, Level 16, State 1, Procedure Trig1, Line 4
Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column.
timestamp is an automatic dat-type and SQL Server will not allow me to explicit insert a record to it while the trigger is fired.
is there any workaround for this without exclude the timestamp column?
the other possibility is to insert a DEFAULT into the timestamp column. how am i doing that?
Thanks
February 23, 2012 at 4:38 am
timestamp is a row versioning method so you cant explicitly give it a value it has to generate it itself, you could set a default but they each row will get the same value.
i take it you just want the date and time it was inserted? if so use a datetime column instead and give it a default of getdate()
or convert the timestamp to a datetime
February 23, 2012 at 4:42 am
Thank you very much.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply