db time conversion: updates

Showing posts with label updates. Show all posts

Wednesday, March 7, 2012

q; stored procedure lowest priority

Hello,
I have a long stored procedure that performs some inserts and updates on the
table that users are accessing through a user interface. We this stored
procedure runs users experience slowness, is there any way I can get the
stored procedure runs with a lowest priority so that user will be able to
perform their tasks first.What tasks do you mean? If the sp is triggered from within the page - then
you could palce some conditionals around the code triggering th sp and only
allow the execution , when certain conditions have been met
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:6A5E92CA-6EAB-42D9-92F3-6BC57CF45CA5@.microsoft.com...
> Hello,
> I have a long stored procedure that performs some inserts and updates on
the
> table that users are accessing through a user interface. We this stored
> procedure runs users experience slowness, is there any way I can get the
> stored procedure runs with a lowest priority so that user will be able to
> perform their tasks first.
>|||Thanks for your reply. Stored procedures are initiated by and Windows
Application. Can you give me example how exactly I can do that?
"Jack Vamvas" wrote:
> What tasks do you mean? If the sp is triggered from within the page - then
> you could palce some conditionals around the code triggering th sp and only
> allow the execution , when certain conditions have been met
>
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> ___________________________________
>
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:6A5E92CA-6EAB-42D9-92F3-6BC57CF45CA5@.microsoft.com...
> > Hello,
> >
> > I have a long stored procedure that performs some inserts and updates on
> the
> > table that users are accessing through a user interface. We this stored
> > procedure runs users experience slowness, is there any way I can get the
> > stored procedure runs with a lowest priority so that user will be able to
> > perform their tasks first.
> >
>
>|||JIM.H. wrote:
> Hello,
> I have a long stored procedure that performs some inserts and updates on the
> table that users are accessing through a user interface. We this stored
> procedure runs users experience slowness, is there any way I can get the
> stored procedure runs with a lowest priority so that user will be able to
> perform their tasks first.
>
You've basically asked the same question about 8 times - why do I have
deadlocking, why does my system run slow, why do I have blocking.
You've been given the same basic answer multiple times - YOUR CODE
AND/OR INDEXES ARE NOT OPTIMIZED.
Your users experience slowness when your procedure is running because
the the updates and inserts are causing blocking. Your goal is simple -
minimize the amount of time that your locks are held. To do that, you
need to thoroughly analyze the queries that you are running, the indexes
that they are using, and make the necessary changes. In one of your
responses to me you basically stated "I don't have time to learn the
right way, I just want a fix". Unfortunately, there is no hidden "magic
setting" that will make everything better.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

q; stored procedure lowest priority

Hello,
I have a long stored procedure that performs some inserts and updates on the
table that users are accessing through a user interface. We this stored
procedure runs users experience slowness, is there any way I can get the
stored procedure runs with a lowest priority so that user will be able to
perform their tasks first.What tasks do you mean? If the sp is triggered from within the page - then
you could palce some conditionals around the code triggering th sp and only
allow the execution , when certain conditions have been met
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:6A5E92CA-6EAB-42D9-92F3-6BC57CF45CA5@.microsoft.com...
> Hello,
> I have a long stored procedure that performs some inserts and updates on
the
> table that users are accessing through a user interface. We this stored
> procedure runs users experience slowness, is there any way I can get the
> stored procedure runs with a lowest priority so that user will be able to
> perform their tasks first.
>|||Thanks for your reply. Stored procedures are initiated by and Windows
Application. Can you give me example how exactly I can do that?
"Jack Vamvas" wrote:

> What tasks do you mean? If the sp is triggered from within the page - then
> you could palce some conditionals around the code triggering th sp and onl
y
> allow the execution , when certain conditions have been met
>
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> ___________________________________
>
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:6A5E92CA-6EAB-42D9-92F3-6BC57CF45CA5@.microsoft.com...
> the
>
>|||JIM.H. wrote:
> Hello,
> I have a long stored procedure that performs some inserts and updates on t
he
> table that users are accessing through a user interface. We this stored
> procedure runs users experience slowness, is there any way I can get the
> stored procedure runs with a lowest priority so that user will be able to
> perform their tasks first.
>
You've basically asked the same question about 8 times - why do I have
deadlocking, why does my system run slow, why do I have blocking.
You've been given the same basic answer multiple times - YOUR CODE
AND/OR INDEXES ARE NOT OPTIMIZED.
Your users experience slowness when your procedure is running because
the the updates and inserts are causing blocking. Your goal is simple -
minimize the amount of time that your locks are held. To do that, you
need to thoroughly analyze the queries that you are running, the indexes
that they are using, and make the necessary changes. In one of your
responses to me you basically stated "I don't have time to learn the
right way, I just want a fix". Unfortunately, there is no hidden "magic
setting" that will make everything better.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Saturday, February 25, 2012

q; exit stored procedure

Hello I have three stored procedures Sp1 calls Sp2 and Sp2 calls Sp3 as
follows:
Exec Sp1
Inserts, updates
Exec Sp2
Inserts, upadtes
Exec Sp3
If error Exit Exec Sp1
Can I end Sp1 if I catch an error in Sp3Jim
CREATE PROCEDURE BigOne
AS
DECLARE @.err integer
BEGIN TRANSACTION
EXEC @.err = sp1
SELECT @.err = coalesce(nullif(@.err, 0, @.@.error)
IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
EXEC @.err = sp2
SELECT @.err = coalesce(nullif(@.err, 0, @.@.error)
IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
EXEC @.err = sp3
SELECT @.err = coalesce(nullif(@.err, 0, @.@.error)
IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
EXEC @.err = sp4
SELECT @.err = coalesce(nullif(@.err, 0, @.@.error)
IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
COMMIT TRANSACION
GO
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:BAFF8A0D-E2CE-462D-AE75-02AEFF9ED379@.microsoft.com...
> Hello I have three stored procedures Sp1 calls Sp2 and Sp2 calls Sp3 as
> follows:
> Exec Sp1
> Inserts, updates
> Exec Sp2
> Inserts, upadtes
> Exec Sp3
> If error Exit Exec Sp1
> Can I end Sp1 if I catch an error in Sp3
>|||Hi Uri,
Thank you very much for your help. I am currently getting Deadlock message
from time to time, I was wondering how this commit rollback will effect it.
Since there will not be auto-commit for each update (I have many updates
before calling SP2, SP3, and one single commit for all the update, will this
increase the chance of deadlock or decrease it?
"Uri Dimant" wrote:
> Jim
> CREATE PROCEDURE BigOne
> AS
> DECLARE @.err integer
> BEGIN TRANSACTION
> EXEC @.err = sp1
> SELECT @.err = coalesce(nullif(@.err, 0, @.@.error)
> IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
> EXEC @.err = sp2
> SELECT @.err = coalesce(nullif(@.err, 0, @.@.error)
> IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
> EXEC @.err = sp3
> SELECT @.err = coalesce(nullif(@.err, 0, @.@.error)
> IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
> EXEC @.err = sp4
> SELECT @.err = coalesce(nullif(@.err, 0, @.@.error)
> IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
> COMMIT TRANSACION
> GO
>
>
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:BAFF8A0D-E2CE-462D-AE75-02AEFF9ED379@.microsoft.com...
> > Hello I have three stored procedures Sp1 calls Sp2 and Sp2 calls Sp3 as
> > follows:
> >
> > Exec Sp1
> > Inserts, updates
> > Exec Sp2
> > Inserts, upadtes
> > Exec Sp3
> > If error Exit Exec Sp1
> >
> > Can I end Sp1 if I catch an error in Sp3
> >
>
>|||JIM
Read this article
http://www.sql-server-performance.com/deadlocks.asp
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:3FE71243-9EAA-4C17-BF27-781C41670925@.microsoft.com...
> Hi Uri,
> Thank you very much for your help. I am currently getting Deadlock message
> from time to time, I was wondering how this commit rollback will effect
> it.
> Since there will not be auto-commit for each update (I have many updates
> before calling SP2, SP3, and one single commit for all the update, will
> this
> increase the chance of deadlock or decrease it?
>
> "Uri Dimant" wrote:
>> Jim
>> CREATE PROCEDURE BigOne
>> AS
>> DECLARE @.err integer
>> BEGIN TRANSACTION
>> EXEC @.err = sp1
>> SELECT @.err = coalesce(nullif(@.err, 0, @.@.error)
>> IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
>> EXEC @.err = sp2
>> SELECT @.err = coalesce(nullif(@.err, 0, @.@.error)
>> IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
>> EXEC @.err = sp3
>> SELECT @.err = coalesce(nullif(@.err, 0, @.@.error)
>> IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
>> EXEC @.err = sp4
>> SELECT @.err = coalesce(nullif(@.err, 0, @.@.error)
>> IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
>> COMMIT TRANSACION
>> GO
>>
>>
>> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
>> news:BAFF8A0D-E2CE-462D-AE75-02AEFF9ED379@.microsoft.com...
>> > Hello I have three stored procedures Sp1 calls Sp2 and Sp2 calls Sp3 as
>> > follows:
>> >
>> > Exec Sp1
>> > Inserts, updates
>> > Exec Sp2
>> > Inserts, upadtes
>> > Exec Sp3
>> > If error Exit Exec Sp1
>> >
>> > Can I end Sp1 if I catch an error in Sp3
>> >
>>|||Thanks Uri, yes I was reading that article, since the commit will be
performed for a long transaction I will probably get more deadlocks in this
case. Do you agree with me?
"Uri Dimant" wrote:
> JIM
> Read this article
> http://www.sql-server-performance.com/deadlocks.asp
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:3FE71243-9EAA-4C17-BF27-781C41670925@.microsoft.com...
> > Hi Uri,
> >
> > Thank you very much for your help. I am currently getting Deadlock message
> > from time to time, I was wondering how this commit rollback will effect
> > it.
> > Since there will not be auto-commit for each update (I have many updates
> > before calling SP2, SP3, and one single commit for all the update, will
> > this
> > increase the chance of deadlock or decrease it?
> >
> >
> >
> > "Uri Dimant" wrote:
> >
> >> Jim
> >> CREATE PROCEDURE BigOne
> >> AS
> >> DECLARE @.err integer
> >> BEGIN TRANSACTION
> >> EXEC @.err = sp1
> >> SELECT @.err = coalesce(nullif(@.err, 0, @.@.error)
> >> IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
> >> EXEC @.err = sp2
> >> SELECT @.err = coalesce(nullif(@.err, 0, @.@.error)
> >> IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
> >> EXEC @.err = sp3
> >> SELECT @.err = coalesce(nullif(@.err, 0, @.@.error)
> >> IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
> >> EXEC @.err = sp4
> >> SELECT @.err = coalesce(nullif(@.err, 0, @.@.error)
> >> IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
> >> COMMIT TRANSACION
> >> GO
> >>
> >>
> >>
> >>
> >>
> >> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> >> news:BAFF8A0D-E2CE-462D-AE75-02AEFF9ED379@.microsoft.com...
> >> > Hello I have three stored procedures Sp1 calls Sp2 and Sp2 calls Sp3 as
> >> > follows:
> >> >
> >> > Exec Sp1
> >> > Inserts, updates
> >> > Exec Sp2
> >> > Inserts, upadtes
> >> > Exec Sp3
> >> > If error Exit Exec Sp1
> >> >
> >> > Can I end Sp1 if I catch an error in Sp3
> >> >
> >>
> >>
> >>
>
>

q; 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?I would NEVER write any code that had table names like T1 or T2, and columns
named F1, F2, F3. Is this real (and I hope not), or do you have table DDL
that you can post?
Give us more to work with, and you may get some good assistance here.
For example, the the code snipplet, is TYPE a column name, a literal, a
varable? Where did it come from?
--
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:27DE8EE3-67B8-4013-ACE5-81AA58C60D22@.microsoft.com...
>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?
>
>|||Hi Arnie,
That was just to simplify the case. I do not have actual table definitions
yet. Type is what I am trying to determine in the trigger. This will be
INSER,UPDATE,DELETE trigger, can I somehow figure it out in the trigger?
"Arnie Rowland" wrote:
> I would NEVER write any code that had table names like T1 or T2, and columns
> named F1, F2, F3. Is this real (and I hope not), or do you have table DDL
> that you can post?
> Give us more to work with, and you may get some good assistance here.
> For example, the the code snipplet, is TYPE a column name, a literal, a
> varable? Where did it come from?
> --
> Arnie Rowland*
> "To be successful, your heart must accompany your knowledge."
>
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:27DE8EE3-67B8-4013-ACE5-81AA58C60D22@.microsoft.com...
> >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?
> >
> >
> >
> >
>
>|||JIM.H. wrote:
> 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?
>
>
I'm guessing this is some sort of audit mechanism? If so, lots of info
available online regarding auditing, my friend Google found this one for me:
http://www.nigelrivett.net/AuditTrailTrigger.html
Incidentally, this type of activity, if done poorly, can cause BLOCKING
on updates and inserts. Just tossing that out there, since you've been
complaining all week about deadlocks.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Yes, inside the Trigger you will be able to determine if it is executing
because of INSERT, UPDATE, DELETE.
If you are exploring some form of auditing, the archive table 'should' have
additional columns for the user and current date/time. Those are also
available internal to the Trigger.
Let us know how to help you when you're closer to the need.
--
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:86BDF37C-B673-473B-9808-36609153D58F@.microsoft.com...
> Hi Arnie,
> That was just to simplify the case. I do not have actual table definitions
> yet. Type is what I am trying to determine in the trigger. This will be
> INSER,UPDATE,DELETE trigger, can I somehow figure it out in the trigger?
>
> "Arnie Rowland" wrote:
>> I would NEVER write any code that had table names like T1 or T2, and
>> columns
>> named F1, F2, F3. Is this real (and I hope not), or do you have table DDL
>> that you can post?
>> Give us more to work with, and you may get some good assistance here.
>> For example, the the code snipplet, is TYPE a column name, a literal, a
>> varable? Where did it come from?
>> --
>> Arnie Rowland*
>> "To be successful, your heart must accompany your knowledge."
>>
>> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
>> news:27DE8EE3-67B8-4013-ACE5-81AA58C60D22@.microsoft.com...
>> >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?
>> >
>> >
>> >
>> >
>>|||Thansk Arnie. Does this do update and insert safely? how can I do deleted?
CREATE TRIGGER trMyTrigger ON T1
FOR INSERT, UPDATE
AS
if exists (select * from inserted)
Insert Into T2(ID, Name,TrType)
Select ID, Name,'Insert'
From inserted
Where Name='TestData'
else
Update t
Set t.Name=i.Name, t.TrType='Update'
From inserted i INNER JOIN T2 t on i.ID=t.ID
GO
"Arnie Rowland" wrote:
> Yes, inside the Trigger you will be able to determine if it is executing
> because of INSERT, UPDATE, DELETE.
> If you are exploring some form of auditing, the archive table 'should' have
> additional columns for the user and current date/time. Those are also
> available internal to the Trigger.
> Let us know how to help you when you're closer to the need.
> --
> Arnie Rowland
> "To be successful, your heart must accompany your knowledge."
>
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:86BDF37C-B673-473B-9808-36609153D58F@.microsoft.com...
> > Hi Arnie,
> >
> > That was just to simplify the case. I do not have actual table definitions
> > yet. Type is what I am trying to determine in the trigger. This will be
> > INSER,UPDATE,DELETE trigger, can I somehow figure it out in the trigger?
> >
> >
> >
> > "Arnie Rowland" wrote:
> >
> >> I would NEVER write any code that had table names like T1 or T2, and
> >> columns
> >> named F1, F2, F3. Is this real (and I hope not), or do you have table DDL
> >> that you can post?
> >>
> >> Give us more to work with, and you may get some good assistance here.
> >>
> >> For example, the the code snipplet, is TYPE a column name, a literal, a
> >> varable? Where did it come from?
> >>
> >> --
> >> Arnie Rowland*
> >> "To be successful, your heart must accompany your knowledge."
> >>
> >>
> >>
> >> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> >> news:27DE8EE3-67B8-4013-ACE5-81AA58C60D22@.microsoft.com...
> >> >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?
> >> >
> >> >
> >> >
> >> >
> >>
> >>
> >>
>
>|||Triggers have access to 2 virtual tables, [inserted] and [deleted]. They are
identical in schema to the action table.
If there is an INSERT, [inserted] will have row(s) and [deleted] will be
empty.
If there is an UPDATE, both will have rows,
[deleted] has the state of the data before the UPDATE, and
[inserted] has the state of data after the UPDATE
If there is a DELETE, [deleted] will have row(s) and [inserted] will be
empty.
So you check both tables to determine what action fired the Trigger.
You probably should refer to Books on Line for additional information.
--
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:EF507C01-C12B-439B-B9A9-856113703E38@.microsoft.com...
> Thansk Arnie. Does this do update and insert safely? how can I do deleted?
> CREATE TRIGGER trMyTrigger ON T1
> FOR INSERT, UPDATE
> AS
> if exists (select * from inserted)
> Insert Into T2(ID, Name,TrType)
> Select ID, Name,'Insert'
> From inserted
> Where Name='TestData'
> else
> Update t
> Set t.Name=i.Name, t.TrType='Update'
> From inserted i INNER JOIN T2 t on i.ID=t.ID
> GO
>
> "Arnie Rowland" wrote:
>> Yes, inside the Trigger you will be able to determine if it is executing
>> because of INSERT, UPDATE, DELETE.
>> If you are exploring some form of auditing, the archive table 'should'
>> have
>> additional columns for the user and current date/time. Those are also
>> available internal to the Trigger.
>> Let us know how to help you when you're closer to the need.
>> --
>> Arnie Rowland
>> "To be successful, your heart must accompany your knowledge."
>>
>> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
>> news:86BDF37C-B673-473B-9808-36609153D58F@.microsoft.com...
>> > Hi Arnie,
>> >
>> > That was just to simplify the case. I do not have actual table
>> > definitions
>> > yet. Type is what I am trying to determine in the trigger. This will be
>> > INSER,UPDATE,DELETE trigger, can I somehow figure it out in the
>> > trigger?
>> >
>> >
>> >
>> > "Arnie Rowland" wrote:
>> >
>> >> I would NEVER write any code that had table names like T1 or T2, and
>> >> columns
>> >> named F1, F2, F3. Is this real (and I hope not), or do you have table
>> >> DDL
>> >> that you can post?
>> >>
>> >> Give us more to work with, and you may get some good assistance here.
>> >>
>> >> For example, the the code snipplet, is TYPE a column name, a literal,
>> >> a
>> >> varable? Where did it come from?
>> >>
>> >> --
>> >> Arnie Rowland*
>> >> "To be successful, your heart must accompany your knowledge."
>> >>
>> >>
>> >>
>> >> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
>> >> news:27DE8EE3-67B8-4013-ACE5-81AA58C60D22@.microsoft.com...
>> >> >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?
>> >> >
>> >> >
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>|||Thanks Tracy, this helps a lot.
"Tracy McKibben" wrote:
> JIM.H. wrote:
> > 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?
> >
> >
> >
> >
> I'm guessing this is some sort of audit mechanism? If so, lots of info
> available online regarding auditing, my friend Google found this one for me:
> http://www.nigelrivett.net/AuditTrailTrigger.html
> Incidentally, this type of activity, if done poorly, can cause BLOCKING
> on updates and inserts. Just tossing that out there, since you've been
> complaining all week about deadlocks.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>

Wednesday, March 7, 2012

q; stored procedure lowest priority

q; stored procedure lowest priority

Saturday, February 25, 2012

q; exit stored procedure

q; a trigger code

q; a trigger code

db time conversion

Blog Archive

About Me