BEFORE Triggers: modified

Showing posts with label modified. Show all posts

Friday, February 24, 2012

Best Approach at Refreshing large table

quote:

> I have a large table that needs to be refreshed on a regular basis. This
> data is read-only, it will not be modified. I import the refreshed data

quote:

> its entirety (all data exists in a fresh table).
> I can think of 3 ways to do the refresh but I'm not sure how to evaluate
> which option is best:
> 1. Import the refreshed table
> Drop the original table
> Rename the refreshed table to original table
> 2. Import the refreshed table
> Truncate the old table
> Insert all the records from the refresh table into the original table
> 3. Import the refreshed table
> Delete all records in the original table that do not exist in the
> refresh table
> Insert all records in the refresh table that do not exist in the
> original table
> Update all records in the original that are not the same as those in

the

quote:

> refresh table
>
> Method 1 might lead to "invalid object" errors if users attempt to access
> the table during the refresh so I do not think this is a good choice.
> I think method 2 and 3 need to be evaluated based on the locks they use

and

quote:

> the time they take to execute.
> What would happen if a user attempts to access the table during the

TRUNCATE

quote:

> and INSERT? Will it be locked from any SELECTs until after the TRUNCATE?
> After the INSERT completes?
> What about option 3? What kind of access will a user have during these 3
> data modification actions INSERT, DELETE and UPDATE?
> As I mentioned, the data is read-only and I need to try to maintain

maximum

quote:

> accessibility for the users.
> Any comments are appreciated.
> Dave
>
>
>

|||Dave,
If availability is the concern, and the data is truly read-only, then here's
what I would do...
Let's make believe you're refreshing "ORDERS"
A1.) Import fresh data into staging table, say it's called "ORDERS_STAGE".
B1.) Set transaction isolation level serializable.
B2.) Begin transaction.
B3.) Rename existing table, say it's called "ORDERS", to "ORDERS_OLD"
B4.) Rename staging table, say it's called "ORDERS_STAGE" to "ORDERS"
B5.) Commit transaction
C1.) Drop "old" table.
This way, the data is only 'unavailable' for milliseconds, and the side
benefit is that, because of the locks acquired, your users will not receive
the 'invalid object name' errors.
I do this in production environments all the time.
Of course, there's a little more to it if you've got DRI and what-not, but
I'm sure you get the idea.
James Hokes
"DaveF" <davef@.comcast.net> wrote in message
news:OEhj0n36DHA.2480@.TK2MSFTNGP10.phx.gbl...

quote:

> I have a large table that needs to be refreshed on a regular basis. This
> data is read-only, it will not be modified. I import the refreshed data

quote:

> its entirety (all data exists in a fresh table).
> I can think of 3 ways to do the refresh but I'm not sure how to evaluate
> which option is best:
> 1. Import the refreshed table
> Drop the original table
> Rename the refreshed table to original table
> 2. Import the refreshed table
> Truncate the old table
> Insert all the records from the refresh table into the original table
> 3. Import the refreshed table
> Delete all records in the original table that do not exist in the
> refresh table
> Insert all records in the refresh table that do not exist in the
> original table
> Update all records in the original that are not the same as those in

the

quote:

> refresh table
>
> Method 1 might lead to "invalid object" errors if users attempt to access
> the table during the refresh so I do not think this is a good choice.
> I think method 2 and 3 need to be evaluated based on the locks they use

and

quote:

> the time they take to execute.
> What would happen if a user attempts to access the table during the

TRUNCATE

quote:

> and INSERT? Will it be locked from any SELECTs until after the TRUNCATE?
> After the INSERT completes?
> What about option 3? What kind of access will a user have during these 3
> data modification actions INSERT, DELETE and UPDATE?
> As I mentioned, the data is read-only and I need to try to maintain

maximum

quote:

> accessibility for the users.
> Any comments are appreciated.
> Dave
>
>
>

|||Hari,
His data is static.
Step 3 would blow chunks in any sort of VLDB situation.
Not to mention that your suggestion of row-level DML goes against the grain
of set-based RDBMS theory.
This is nothing more than a simple table-swap op.
James Hokes|||Excellent!
Thanks very much.
"James Hokes" <noemail@.noway.com> wrote in message
news:e4$vP846DHA.452@.TK2MSFTNGP11.phx.gbl...

quote:

> Dave,
> If availability is the concern, and the data is truly read-only, then

here's

quote:

> what I would do...
> Let's make believe you're refreshing "ORDERS"
> A1.) Import fresh data into staging table, say it's called "ORDERS_STAGE".
> B1.) Set transaction isolation level serializable.
> B2.) Begin transaction.
> B3.) Rename existing table, say it's called "ORDERS", to "ORDERS_OLD"
> B4.) Rename staging table, say it's called "ORDERS_STAGE" to "ORDERS"
> B5.) Commit transaction
> C1.) Drop "old" table.
> This way, the data is only 'unavailable' for milliseconds, and the side
> benefit is that, because of the locks acquired, your users will not

receive

quote:

> the 'invalid object name' errors.
> I do this in production environments all the time.
> Of course, there's a little more to it if you've got DRI and what-not, but
> I'm sure you get the idea.
> James Hokes
> "DaveF" <davef@.comcast.net> wrote in message
> news:OEhj0n36DHA.2480@.TK2MSFTNGP10.phx.gbl...
This[QUOTE]
data[QUOTE]
> in
table[QUOTE]
> the
access[QUOTE]
> and
> TRUNCATE
TRUNCATE?[QUOTE]
3[QUOTE]
> maximum
>

Best Approach at Refreshing large table

I have a large table that needs to be refreshed on a regular basis. This
data is read-only, it will not be modified. I import the refreshed data in
its entirety (all data exists in a fresh table).
I can think of 3 ways to do the refresh but I'm not sure how to evaluate
which option is best:
1. Import the refreshed table
Drop the original table
Rename the refreshed table to original table
2. Import the refreshed table
Truncate the old table
Insert all the records from the refresh table into the original table
3. Import the refreshed table
Delete all records in the original table that do not exist in the
refresh table
Insert all records in the refresh table that do not exist in the
original table
Update all records in the original that are not the same as those in the
refresh table
Method 1 might lead to "invalid object" errors if users attempt to access
the table during the refresh so I do not think this is a good choice.
I think method 2 and 3 need to be evaluated based on the locks they use and
the time they take to execute.
What would happen if a user attempts to access the table during the TRUNCATE
and INSERT? Will it be locked from any SELECTs until after the TRUNCATE?
After the INSERT completes?
What about option 3? What kind of access will a user have during these 3
data modification actions INSERT, DELETE and UPDATE?
As I mentioned, the data is read-only and I need to try to maintain maximum
accessibility for the users.
Any comments are appreciated.
DaveHi,
If you need to provide maximum accssibility to user then go for step3.
Ensure that when ever you do a DML, do it row level.
So only that record will be locked and users will be able to view data with
out any problems.
Thanks
Hari
MCDBA
"DaveF" <davef@.comcast.net> wrote in message
news:OEhj0n36DHA.2480@.TK2MSFTNGP10.phx.gbl...
> I have a large table that needs to be refreshed on a regular basis. This
> data is read-only, it will not be modified. I import the refreshed data
in
> its entirety (all data exists in a fresh table).
> I can think of 3 ways to do the refresh but I'm not sure how to evaluate
> which option is best:
> 1. Import the refreshed table
> Drop the original table
> Rename the refreshed table to original table
> 2. Import the refreshed table
> Truncate the old table
> Insert all the records from the refresh table into the original table
> 3. Import the refreshed table
> Delete all records in the original table that do not exist in the
> refresh table
> Insert all records in the refresh table that do not exist in the
> original table
> Update all records in the original that are not the same as those in
the
> refresh table
>
> Method 1 might lead to "invalid object" errors if users attempt to access
> the table during the refresh so I do not think this is a good choice.
> I think method 2 and 3 need to be evaluated based on the locks they use
and
> the time they take to execute.
> What would happen if a user attempts to access the table during the
TRUNCATE
> and INSERT? Will it be locked from any SELECTs until after the TRUNCATE?
> After the INSERT completes?
> What about option 3? What kind of access will a user have during these 3
> data modification actions INSERT, DELETE and UPDATE?
> As I mentioned, the data is read-only and I need to try to maintain
maximum
> accessibility for the users.
> Any comments are appreciated.
> Dave
>
>
>|||Dave,
If availability is the concern, and the data is truly read-only, then here's
what I would do...
Let's make believe you're refreshing "ORDERS"
A1.) Import fresh data into staging table, say it's called "ORDERS_STAGE".
B1.) Set transaction isolation level serializable.
B2.) Begin transaction.
B3.) Rename existing table, say it's called "ORDERS", to "ORDERS_OLD"
B4.) Rename staging table, say it's called "ORDERS_STAGE" to "ORDERS"
B5.) Commit transaction
C1.) Drop "old" table.
This way, the data is only 'unavailable' for milliseconds, and the side
benefit is that, because of the locks acquired, your users will not receive
the 'invalid object name' errors.
I do this in production environments all the time.
Of course, there's a little more to it if you've got DRI and what-not, but
I'm sure you get the idea.
James Hokes
"DaveF" <davef@.comcast.net> wrote in message
news:OEhj0n36DHA.2480@.TK2MSFTNGP10.phx.gbl...
> I have a large table that needs to be refreshed on a regular basis. This
> data is read-only, it will not be modified. I import the refreshed data
in
> its entirety (all data exists in a fresh table).
> I can think of 3 ways to do the refresh but I'm not sure how to evaluate
> which option is best:
> 1. Import the refreshed table
> Drop the original table
> Rename the refreshed table to original table
> 2. Import the refreshed table
> Truncate the old table
> Insert all the records from the refresh table into the original table
> 3. Import the refreshed table
> Delete all records in the original table that do not exist in the
> refresh table
> Insert all records in the refresh table that do not exist in the
> original table
> Update all records in the original that are not the same as those in
the
> refresh table
>
> Method 1 might lead to "invalid object" errors if users attempt to access
> the table during the refresh so I do not think this is a good choice.
> I think method 2 and 3 need to be evaluated based on the locks they use
and
> the time they take to execute.
> What would happen if a user attempts to access the table during the
TRUNCATE
> and INSERT? Will it be locked from any SELECTs until after the TRUNCATE?
> After the INSERT completes?
> What about option 3? What kind of access will a user have during these 3
> data modification actions INSERT, DELETE and UPDATE?
> As I mentioned, the data is read-only and I need to try to maintain
maximum
> accessibility for the users.
> Any comments are appreciated.
> Dave
>
>
>|||Hari,
His data is static.
Step 3 would blow chunks in any sort of VLDB situation.
Not to mention that your suggestion of row-level DML goes against the grain
of set-based RDBMS theory.
This is nothing more than a simple table-swap op.
James Hokes|||Excellent!
Thanks very much.
"James Hokes" <noemail@.noway.com> wrote in message
news:e4$vP846DHA.452@.TK2MSFTNGP11.phx.gbl...
> Dave,
> If availability is the concern, and the data is truly read-only, then
here's
> what I would do...
> Let's make believe you're refreshing "ORDERS"
> A1.) Import fresh data into staging table, say it's called "ORDERS_STAGE".
> B1.) Set transaction isolation level serializable.
> B2.) Begin transaction.
> B3.) Rename existing table, say it's called "ORDERS", to "ORDERS_OLD"
> B4.) Rename staging table, say it's called "ORDERS_STAGE" to "ORDERS"
> B5.) Commit transaction
> C1.) Drop "old" table.
> This way, the data is only 'unavailable' for milliseconds, and the side
> benefit is that, because of the locks acquired, your users will not
receive
> the 'invalid object name' errors.
> I do this in production environments all the time.
> Of course, there's a little more to it if you've got DRI and what-not, but
> I'm sure you get the idea.
> James Hokes
> "DaveF" <davef@.comcast.net> wrote in message
> news:OEhj0n36DHA.2480@.TK2MSFTNGP10.phx.gbl...
> > I have a large table that needs to be refreshed on a regular basis.
This
> > data is read-only, it will not be modified. I import the refreshed
data
> in
> > its entirety (all data exists in a fresh table).
> >
> > I can think of 3 ways to do the refresh but I'm not sure how to evaluate
> > which option is best:
> >
> > 1. Import the refreshed table
> > Drop the original table
> > Rename the refreshed table to original table
> >
> > 2. Import the refreshed table
> > Truncate the old table
> > Insert all the records from the refresh table into the original
table
> >
> > 3. Import the refreshed table
> > Delete all records in the original table that do not exist in the
> > refresh table
> > Insert all records in the refresh table that do not exist in the
> > original table
> > Update all records in the original that are not the same as those in
> the
> > refresh table
> >
> >
> > Method 1 might lead to "invalid object" errors if users attempt to
access
> > the table during the refresh so I do not think this is a good choice.
> >
> > I think method 2 and 3 need to be evaluated based on the locks they use
> and
> > the time they take to execute.
> >
> > What would happen if a user attempts to access the table during the
> TRUNCATE
> > and INSERT? Will it be locked from any SELECTs until after the
TRUNCATE?
> > After the INSERT completes?
> >
> > What about option 3? What kind of access will a user have during these
3
> > data modification actions INSERT, DELETE and UPDATE?
> >
> > As I mentioned, the data is read-only and I need to try to maintain
> maximum
> > accessibility for the users.
> >
> > Any comments are appreciated.
> > Dave
> >
> >
> >
> >
> >
>

Friday, February 10, 2012

Before Insert

Hi,

Im migrating an interbase database across to sql server 2005 the problem I have is that it uses before triggers which allow data to be modified before the table is populated. So if a large value was inserted into a smaller data type it will scale the value down.

The idea is to not change the front end if at all possible is there a way that I can mimick this behaviour.

I've tried triggers but the insert fails before it gets to the trigger the same with using instead of triggers as I believe the insert table is identical to the base table and so this fails.

I've looked at rules but this just allows me to restrict the vales going in?.

Any ideas would be appreciated.

Many thanks.

Code Snippet

-- Rename your base table
Create table RenamedTable (
Srno int,
descr varchar(30)
)

-- Create a view with same name as your base table
alter view BaseTable
as
select
Srno, Descr = convert(varchar(50), Descr )
from
RenamedTable -- This is your Original/Renamed table

--Create an INSTEAD OF INSERT trigger on the view.
ALTER TRIGGER trI_BaseTable on BaseTable
INSTEAD OF INSERT
AS
BEGIN
set nocount on
INSERT INTO RenamedTable
SELECT Srno, left(Descr,30) FROM inserted
END
GO

insert into BaseTable select * from BaseTable
insert into BaseTable values (1, 'One')
insert into BaseTable values (2, 'Two123123123123123123123123123123123123123123123')
select * from RenamedTable.

|||Refer to Books Online, Topic: 'INSTEAD OF TRIGGERS'|||

SunnyD,

I'm sure there's more to the picture than meets the eye, but I have a couple of questions.

1. If a value is larger than the datatype of the field, how are you scaling it down?

2. When you scale it down, are you losing value or simply trimming the fat?

The reason I ask is that the frequency of scaling down may justify the means to utlimately increase the datatype size for the field.

Triggers are often a reasonable solution but we have to be careful that we don't abuse the intent.

Also, the filtering can be accomplished at the source instead of waiting to trim at the database level. I realize you don't want to change the front end, but this is where validation should occur. This helps eliminate the GIGO (garbage in, garbage out) potential.

In closing, although databases do have the functionality to clean house, it doesn't mean that we should neglect the programming practice of using validation on the front end. Remember, databases were designed as a storage facility, not a programming environment.

Just my twist on it,

Adamus

|||

Totally agree with everything said and given the choice I would rather change the front-end however that's not an option at the moment and my scope is to get the back-end working in an identical fashion to the way it works now. I've tried instead of triggers but again this does not appear to work see example below:

CREATETABLE BaseTable

(OrderKey intPRIMARYKEYIDENTITY(1,1),

Quantity smallintNOTNULL)

--Create a view that contains all columns from the base table.

CREATEVIEW InsteadView

SELECT OrderKey, Quantity

FROM BaseTable

--Create an INSTEAD OF INSERT trigger on the view.

CREATETRIGGER InsteadTrigger on InsteadView

INSTEADOFINSERT

BEGIN

--Build an INSERT statement

INSERTINTO BaseTable(Quantity)

SELECTCASEWHEN Quantity > 99999 THEN Quantity/10.0 ELSE Quantity END

FROM inserted

END

INSERTINTO InsteadView (Quantity)SELECT 9999 --Works ok

INSERTINTO InsteadView (Quantity)SELECT 99999

Msg 220,Level 16,State 1, Line 1

Arithmetic overflow error for data typesmallint,value= 99999.

The statement has been terminated.

Any other thought guys?

|||

smallint is defined as a value between -32768 and 32767. So, 99999 definitely causes overflow.

Either change your datatype or change your case/when to trap the correct range.

e.g.

SELECT CASE WHEN Quantity > 32767 THEN Quantity/10.0 ELSE Quantity END

|||

If data type not changed changing case will not work! Im still getting overflow as inserted will be based on the base table and therefore will not hold a value greater than 32,767. i.e. Quantity column in inserted will not store a value greater than 32767.

|||

The VIEW has the SAME datatypes as the underlaying table.

You are attempting to INSERT a value larger than 32767 into the VIEW and it will FAIL since the datatype is smallint.

The only way that you will be able to accomplish this task is to create another table with a larger datatype, and use a trigger on that table to move the data to your original table.

In my opinion, a very bad kludge... (Change the original table's datatype and stop perverting the data.)

|||I believe you can create a view with different data type (length)

Create table BaseTable (Srno int, varchar(30))
go

Create view my View as
select
Srno, Descr = convert(varchar(50), Descr )
from
BaseTable
go

Now you can create a instead of trigger on this view...

|||

Bushan,

I'm not too sure that will work for an INSERT. Have you tried it and been successful?

|||

Code Snippet

-- Rename your base table
Create table RenamedTable (
Srno int,
descr varchar(30)
)

-- Create a view with same name as your base table
alter view BaseTable
as
select
Srno, Descr = convert(varchar(50), Descr )
from
RenamedTable -- This is your Original/Renamed table

--Create an INSTEAD OF INSERT trigger on the view.
CREATE TRIGGER trI_BaseTable on BaseTable
INSTEAD OF INSERT
AS
BEGIN
set nocount on
INSERT INTO RenamedTable
SELECT Srno, left(Descr,30) FROM inserted
END
GO

insert into BaseTable select * from BaseTable
insert into BaseTable values (1, 'One')
insert into BaseTable values (2, 'Two123123123123123123123123123123123123123123123')
select * from RenamedTable.

|||

I should have been a bit more explicit. The OP's table DDL indicates the presence of an IDENTITY field.

Because of the IDENTITY field, the code suggestion you provided doesn't seem to work as presented to solve the OP's issue.

I'm trying to understand if you have created a 'work-around' for handling the absence of the IDENTITY value in inserted. Even setting IDENTITY_INSERT ON in the Trigger doesn't seem to allow a way to get around the absence of the IDENTITY value in inserted. But I'm hoping you have found a way...

...Inquiring minds want to know...

|||

Code Snippet

Here you go...

-- Rename your base table
Create table NewDepartment (
DeptID int identity(1,1),
Dname varchar(6),
Location varchar(20)
)

-- Create a view with same name as your base table
create view Department
as
select
DeptID,
Dname = convert(varchar(50), Dname ),
Location
from
NewDepartment -- This is your Original/Renamed table

--Create an INSTEAD OF INSERT trigger on the view.
CREATE TRIGGER trI_Department on Department
INSTEAD OF INSERT
AS
BEGIN
set nocount on
INSERT INTO NewDepartment (Dname, Location)
SELECT
left(Dname,6), Location
FROM
inserted
END
GO

insert into Department values (1,'Sales', 'California')
insert into Department values (2,'Marketing', 'NewYork') -- 'ing' will be truncated from Marketing

|||

Here is a kludge that changes the underline datatype of the view to allow large value.

--Create a view that contains all columns from the base table.

CREATEVIEW InsteadView

SELECT OrderKey, cast(Quantity as bigint) [Quantity]

FROM BaseTable

--Create an INSTEAD OF INSERT trigger on the view.

CREATETRIGGER InsteadTrigger on InsteadView

INSTEADOFINSERT

BEGIN

--Build an INSERT statement

INSERTINTO BaseTable(Quantity)

SELECTCASEWHEN Quantity > 32676 THEN Quantity/10.0 ELSE Quantity END

FROM inserted

END

|||

Thanks for taking the time and effort.

It is so much more helpful when we provide the OP a response with a suggested solution that actually solves his/her problem.

Friday, February 24, 2012

Best Approach at Refreshing large table

Best Approach at Refreshing large table

Friday, February 10, 2012

Before Insert

BEFORE Triggers

Blog Archive

About Me