Showing posts with label destination. Show all posts
Showing posts with label destination. Show all posts

Tuesday, March 20, 2012

a trigger code

I have table T1 and I am trying to extract some date from T1 table based on inserts, updates and deletes. The destination table T2 has three fields F1, F2, F3, so I need something like

Insert into T2 (F1, F2, F3)

Select (F1, F2,Type)

From T1

Type should be defined based on Insert, Update, or Delete. This will be my first trigger, Can anyone write this trigger for me?

If you want to determine type of trigger action from single trigger then see code below:

set nocount on
go
if object_id('tr_tbl') is not null
drop table tr_tbl
go
create table tr_tbl(i int)
go
create trigger tr_test on tr_tbl for insert, update, delete as
if exists(select * from inserted) and exists(select * from deleted)
print 'Update...' -- set @.type = 'U'
else
if exists(select * from inserted)
print 'Insert...' -- set @.type = 'I'
else
print 'Delete...' -- set @.type = 'D'

... your code
go

insert tr_tbl values(1)
update tr_tbl set i = i + 1
delete tr_tbl

go

drop table tr_tbl

go

But it might be easier writing separate triggers. And you shouldn't query the base table T1 directly since you will get all rows in the table and not just the rows affected by the DML. You need to use the inserted/deleted tables based on the trigger action.

|||

This helps a lot, thanks,

One question: since there are more than one user on the system will there be a case that insert virtual table might have more than one row?

|||Yes, you should always write your trigger code such that it handles multiple rows. This will also force you to use simple set of DMLs from trigger for the logic which is more efficient than a procedural approach. If you want to enforce only singleton operations via DML then you can do so within trigger by checking for @.@.ROWCOUNT and rolling back the transaction/ DML operation.sql

a trigger code

I have table T1 and I am trying to extract some date from T1 table based on inserts, updates and deletes. The destination table T2 has three fields F1, F2, F3, so I need something like

Insert into T2 (F1, F2, F3)

Select (F1, F2,Type)

From T1

Type should be defined based on Insert, Update, or Delete. This will be my first trigger, Can anyone write this trigger for me?

you already have the code you need. you just need to put it in a trigger for which the syntax can be found in BOL.

Saturday, February 11, 2012

A Gripe about Error Messages

[OLE DB Destination [255]] Error: The "input "OLE DB Destination Input" (268)" failed because error code 0xC020907B occurred, and the error row disposition on "input "OLE DB Destination Input" (268)" specifies failure on error. An error occurred on the specified object of the specified component.

I've condensed the useful information in the statement down to the following:

"An error occured."

I'd like to also provide a plain english paraphrase.

An error occured somewhere to something. This means that something somewhere didn't work right. The cause of the thing not working right is an error of some sort. We'd like to provide you with the following piece of diagnostic information: we know that an error occured somewhere to something because the error row disposition tells us this. We hope that helps. Thank you, and have a nice day.

Now, could anyone translate this into Klingon? I think it would be easier to understand and just as useful.

I agree with JO. Most of the SSIS error messages seem to be Vague and Unhelpful. It takes lot of time for a SSIS Dev to decipher the error messsages. The SSIS team should throw more meaningful and error messages that we all can understand and take action appropriately. Errors of the kind mentioned do not help in resolving the issues.

Thanks

AK

|||

Rename your components and look to the first error thrown from a given task for diagnostic/debugging purposes.

Leaving pipeline components at their default names makes debugging far harder. Renaming inputs and outputs is not usually done, because most components, provided they aren't sources, have a single input.

Jamie Thompson has a very useful naming convention , which if followed, will make that error message, and really, the ones which preceeded it, more meaningful, because the component name will not be the default. That doesn't make 0xC0... hex code any more meaningful, but I can tell you it does help.

Those creating somewhat permanent to permanent table names rarely leave the table at dbo.Table_1 because the name doesn't convey intent/purpose. Same concept goes for pipeline components, give them a meaningful name.

Was that the first error received? The first error is almost invariably the most helpful one, and its doubtful that was the first error thrown by the dataflow. Some of the first errors are still not as meaningful as I would like, but often this has to do with the provider, such as an OLEDB provider, which produces the messages which SSIS relays. Before imagining these are the words of an apologist, perhaps note my prior gripes about error messages as well.

Subsequent errors, particuarly in dataflows, are not as useful in debugging/diagnostics, because they are further and futher removed from the specificity and purpose of the component, and relate to how the pipeline works. Look (generally speaking) to the first error for any given task invocation.