INSTEAD OF TRIGGER + VIEW

Question

Post reply

INSTEAD OF TRIGGER + VIEW

karthik M

One Orange Chip

Points: 29139
More actions
February 2, 2011 at 12:24 am

#388757

All,
I have one scenario.
CREATE TABLE emp
(
eno INT
)
go
CREATE TABLE emp_his
(
eno INT
)
go
CREATE VIEW v1
AS SELECT eno FROM emp
CREATE TRIGGER v1_trg ON v1
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO emp
SELECT * FROM inserted
INSERT INTO emp_his
SELECT * FROM inserted
END
when I run the below code from the management studio
insert into v1
select 1
I see this record on both the table. i.e emp & emp_his
But...when i run this view from SSIS, I see the data only in emp table. The same data is not get inserted into emp_his table. How? is this the limitation of SSIS? or do i need to change any properties?
SSIS Job Design:
Control Flow --> DataFlow Task
Data Flow --> Flat File Destination ------> OLEDB Destination (sql 2005)
Inputs are welcome!
karthik

Viewing 5 posts - 1 through 5 (of 5 total)

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

Somethiing must be failing from SSIS.

The code in your trigger has no transaction control,so that it can insert data on table emp and tien fail.

I would suggest adding transaction control to' the trigger and then trac e the SSIS package execution with profiler.

Hope this helps.

Gianluca

-- Gianluca Sartori

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 2

Is FIRE_TRIGGERS specified for your OLEDB destination? By default the bulk inserts do not fire triggers, if you want them to you need to specify it.

Google terms - Fire_Triggers SSIS OLEDB

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

GilaMonster (2/2/2011)
Is FIRE_TRIGGERS specified for your OLEDB destination? By default the bulk inserts do not fire triggers, if you want them to you need to specify it.
Google terms - Fire_Triggers SSIS OLEDB

Ah! Nice catch, Gail.

-- Gianluca Sartori

karthik M One Orange Chip Points: 29139 More actions · Answer 4

Gail,

Wow! It is working perfectly:)

Thanks a lot

karthik