SERIALIZABLE Locks Inside A Trigger?

Oct 25, 2001

I have an insert trigger which has to insert new records into 2 other databases. The first thing it has to do is get a maxid from a table in each of the databases before the insert. i was going to use a SET TRANSACTION ISOLATION LEVEL SERIALIZABLE statment before getting the ID and then do my insert. My question is two-fold, first is this the best way to do it. And since I have 2 separate selects to get the 2 maxids in the other 2 databases and tables, do I need 2 of these statements inside the trigger, before each insert, or a separate one for each select?
Thanks

View 2 Replies


ADVERTISEMENT

Problem With OR Inside A Trigger

Dec 9, 2007

I created the following trigger to ensure that Mastercard or Visa are entered as the credit card type is a credit card table. The trigger works fine when I use one type, but when I add or, it doesn't work. Any advice? Thanks



/* THIS TRIGGERS VERIFIES THAT A VALID CREDIT CARD TYPE IS ENTERED*/

CREATE TRIGGER trg_accepted_credit_card
on credit_card_payments
for insert
as
begin

declare @card_type varchar(50)

select @card_type = (select card_type from inserted)

if exists(select 1 from inserted where @card_type != 'Mastercard' OR @card_type != 'Visa')

begin
print 'Invalid Credit Card Type.'
rollback transaction
end
end

View 2 Replies View Related

Sp_addrolemember Inside A Trigger

Mar 27, 2006

I'm trying to handle some user management inside a trigger. When I call sp_addrolemember I get this error.



sp_addrolemember cannot be used inside a user-defined transaction



Is there any way to get around this error? I need to assign a custom role based on a variable inside this trigger.

Any help is appreciated.

View 5 Replies View Related

Need Total To Reset Inside A Trigger

Jun 2, 2008

Have the following trigger:

CREATE TRIGGER [UPDATEEGBKMUTREBATERECORDS] ON [dbo].[GBKMUT]
after INSERT
AS
begin
DECLARE@Sum Float
SELECT@Sum = SUM(bdr_hfl)
FROMinserted
WHEREfreefield3 = 'Rebate'

SET@Sum = COALESCE(@Sum, 0)
UPDATEgbkmut
SETbdr_hfl = bdr_hfl - @Sum
WHEREreknr = ' 1040'
end

I need @Sum to reset to zero if the ord_no changes. I'm inserting discount records that need to subtract from another account's amount.
As records are inserted for one ord_no and Inv_no I need to sum bdr_hfl field. When the ord_no and Inv_no change, I need to subtract that from the amount in the bdr_hfl where the account number is 1040 and the inserted records ord_no and Inv_no match the ord_no and Inv_no where the account is 1040. Then I need the @sum to reset to zero and start summing again.

View 5 Replies View Related

Getting Primary Key Of The Updated Row In A Table Inside Trigger

May 16, 2008

Hi All,
I am using a trigger. I want to get the data of a row before updating inside this trigger and insert it into a backup table. Please anybody help me. Example with code is highly appreciated.

Thanks in advance.

View 3 Replies View Related

Procedure Execution Sequence Inside Trigger

Feb 17, 2014

How the procedure will be called inside the trigger,whether first procedure followed by second or parallel it will execute?

CREATE TRIGGER [dbo].[InsertDatFXActualStaging]
ON [dbo].[DatFXActualStaging]--change this table to DatFXActualStaging
for INSERT
AS
BEGIN
SET NOCOUNT ON;

[Code] ....

View 1 Replies View Related

Using SCOPE_IDENTITY Inside A COM+ Component (Trigger Involved)

Jan 7, 2008



Hi,

I'm experiencing a problem I think I should not in my COM+ application. I will describe the setup first and then will expose the problem.

It's a simple COM+ application (dll). Inside it, there's a method to save an object A. Object A is persisted in a table in SQL Server 2000 that uses an identity field for the primary key. What this method does is the following:
1) Insert the record for Object A via ADO
2) Retrieve the Id for the object using SCOPE_IDENTITY via ADO and set it on the object
3) Execute an UPDATE statement based on a certain condition via ADO (this UPDATE statement will fire a trigger, however the trigger will not do anything since the record does not answer the criteria for the trigger)
4) Insert a record for another Object A via ADO
5) Retrieve the Id for the object using SCOPE_IDENTITY via ADO and set it on the object

When I get to step (5), an error is raised because SCOPE_IDENTITY returns NULL. This is as if it was returning the Identity value for the trigger that did not cause any INSERT on the UPDATE statement in (3). All the steps are performed using a single connection.

The trigger will duplicate the updated record in another table if a certain flag is set, so in my case, it was not set yet.

It's just weird that this would happen. If I delete the trigger, everything works fine. @@IDENTITY gives me the same problem. It's really as if the trigger was taking over or something and unless I put something between the two steps I get this error. There's one thing though. In step (3), I was using the adCmdText flag for the ADO statement. If I use adExecuteNoRecords it works fine. However I don't understand why it would be this way, I'm trying to understand why it's not working to begin with, even though the sequence of the steps performed should.

Any idea why this would happen?

Thanks,

Greg

View 3 Replies View Related

SQL Server 2008 :: Row Locks Not Escalating To Table Locks After 5000

Jul 16, 2015

I've got an INSERT that's selecting data from a linked server and attempting to push 10 million rows into the blank table. More or less, it looks like this:

insert into ReceivingTable (
Field1, Field2, Field3, Field4
, Field5, Field6, Field7, Field8
, Field9, Field10, Field11, Field12
, Field13, Field14, Field15

[code]...

The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions. There are no other active users. I ran it again and monitored the following DMO to watch the growth of locks for that spid:

SELECT request_session_id, COUNT (*) num_locks
-- select *
FROM sys.dm_tran_locks
--where request_session_id = 77
GROUP BY request_session_id
ORDER BY count (*) DESC

The number of locks started small and held for a while around 4-7 locks, but at about 5 minutes in the number of locks held by that spid grew dramatically to more than 8 million before finally erroring again with the same message. Researching, I can't figure out why it's not escalating from row locks to table locks at the appropriate threshold. The threshold in was set to 0 at first (Server Properties > Advanced > Parallelism > Locks). I set it to 5000, and it still didn't seem to work. Rewriting the INSERT to include a WITH (TABLOCK) allows it to finish successfully in testing. My problem is that it's coming out of an ETL with source code that I can't edit. I need to figure out how to force it to escalate to locking the entire table via table or server level settings.

A colleague suggested that installing service packs may take care of it (the client is running SQL Server 2008 R2 (RTM)), but I haven't found anything online to support that theory.

View 9 Replies View Related

Transact SQL :: How To List All Locks (including NON-BLOCKING Locks)

Aug 5, 2015

We are migrating our database(s) from ORACLE to SQL. In Oracle we were able to issue a SELECT statement and see all of the locks (Blocking and Non-Blocking) currently in the system.  The query also included the Process ID of the process we needed to kill in order to get rid of the lock.

We now need to create the same type of query for Microsoft SQL Server 2012. I have seen postings on different sites saying that this info can be obtained using SP_WHO2 or using the SQL Server Management Studio Activity Monitor's PROCESSES tab, but we are looking for a SELECT statement that will give us similar information.

View 7 Replies View Related

Transact SQL :: Update Inside Trigger Fails Because Of Incorrect Settings

Aug 11, 2015

I'm updating one column using trigger but i am getting below error .

UPDATE failed because the following SET options have incorrect settings: 'NUMERIC_ROUNDABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

View 2 Replies View Related

SERIALIZABLE TRANSACTION Getting Blocked?

Jun 10, 2004

Hi guys,

I have a stored procedure which generates the next sequence number...
it uses SERIALIZABLE Option. procs look something like below..

begin
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION

Sequence generating statement...

COMMIT TRANSACTION
set @NextSequenceValue = @NextSequenceValue
Return @NextSequenceValue
end

For some reason when i call the proc with below parameters to get next sequence number.. its hungs up..


declare @NextSequenceValue int
set @NextSequenceValue = 0
exec spGetNextSequence 19, 'LotSequence', @NextSequenceValue output, Null
select @NextSequenceValue as NextSequenceValue


When i queried sp_who2 it shows that my processid is blocked by some other processid.. and when i do DBCC INPUTBUFFER (blockingprocessid), the query of blocking processid and my nextsequence generation stored proc is not realted at all..

Can you help shed some light on why my nextsequence generating proc is getting hunged...?

help is appericated..

View 4 Replies View Related

Isolation Level Serializable

Jul 4, 2005

Hi all,
can anyone give me more information on
set transaction isolation level serializable ?? I want to prove some lock to use on online insert and update.
Thank you every much.

View 14 Replies View Related

Serializable Transactions And Insert.

Jul 23, 2005

Hi,I have a problem using serializable transactions. In one transaction Iselect a single specific row from a table using a where clause (thiscauses it to acquire and hold a range lock). In another transaction Iattempt to insert a new row into the table (which does not match thefirst transactions where clause), but it is blocked by the firsttransaction. The reading I have done on SQL Server suggests that Ishould be able to insert rows, as long as the new rows do not match thewhere clause in the other transactions select.Here is what I do:Transaction 1SET TRANSACTION ISOLATION LEVEL SerializableBEGIN TRANSACTIONselect * from test_table where id=1;and then on another session I runTransaction 2SET TRANSACTION ISOLATION LEVEL SerializableBEGIN TRANSACTIONinsert into test_table values (2, 'two');Transaction 2 cannot complete until transaction 1 has finished. I havetried using WITH (ROWLOCK) hints but to no avail. Am I missingsomething important? Is this true of other DBs?(I am able to update rows that are not in transaction 1's where clause)I am using SQL Server version 8.00.760(SP3).Thanks for your help,Magnus.

View 3 Replies View Related

Serializable Read And Lock Mode

Jul 20, 2005

Hi,Version Info: SQLSERVER 2000 SP3I am trying to understand how SQL Server works with SERIALIZABLE read.I am fairly new to SQL Server, having mainly worked with Informix.We are getting lock timeout error in our application. Our lock timeoutis set to 20 seconds. I am trying to investigate why locks are held forsuch a long time in the first place.I believe SQLServer tries to put keylocks as much as possible forensuring serializibility. For e.g.SELECT column_bfrom table_awhere column_c between 1000 and 1100what if column_c has no index. Does it switch over the data row.And what if a table has no index. Will it go for table lock, as ituse to do in earlier versions.TIA.--email id is bogus

View 3 Replies View Related

SQL Server 2005 JDBC Driver V1.2: StreamError Not Serializable

Jan 11, 2008

I'm encountering a problem that seems to be due to the StreamError class not being serializable. This was raised on the JDBC Driver Team blog by Jonas on 10/24/2007. Jimmy Wu notes on 10/29/2007 that it is under investigation. Any progress?

http://blogs.msdn.com/jdbcteam/archive/2007/10/12/microsoft-sql-server-2005-jdbc-driver-v1-2-official-release.aspx#comments

Thanks,
Mike

View 2 Replies View Related

Do GetDate() Inside SQL Server OR Do System.DateTime.Now Inside Application ?

Sep 12, 2007

For inserting current date and time into the database, is it more efficient and performant and faster to do getDate() inside SQL Server and insert the value
OR
to do System.DateTime.Now in the application and then insert it in the table?
I figure even small differences would be magnified if there is moderate traffic, so every little bit helps.
Thanks.

View 9 Replies View Related

EXEC Inside CASE Inside SELECT

Nov 16, 2007

I'm trying to execute a stored procedure within the case clause of select statement.
The stored procedure returns a table, and is pretty big and complex, and I don't particularly want to copy the whole thing over to work here. I'm looking for something more elegant.

@val1 and @val2 are passed in


CREATE TABLE #TEMP(
tempid INT IDENTITY (1,1) NOT NULL,
myint INT NOT NULL,
mybool BIT NOT NULL
)

INSERT INTO #TEMP (myint, mybool)
SELECT my_int_from_tbl,
CASE WHEN @val1 IN (SELECT val1 FROM (EXEC dbo.my_stored_procedure my_int_from_tbl, my_param)) THEN 1 ELSE 0
FROM dbo.tbl
WHERE tbl.val2 = @val2


SELECT COUNT(*) FROM #TEMP WHERE mybool = 1


If I have to, I can do a while loop and populate another temp table for every "my_int_from_tbl," but I don't really know the syntax for that.

Any suggestions?

View 8 Replies View Related

Differentiate Between Whether Stored Procedure A Is Executed Inside Query Analyzer Or Executed Inside System Application Itself.

May 26, 2008

Just wonder whether is there any indicator or system parameters that can indicate whether stored procedure A is executed inside query analyzer or executed inside application itself so that if execution is done inside query analyzer then i can block it from being executed/retrieve sensitive data from it?

What i'm want to do is to block someone executing stored procedure using query analyzer and retrieve its sensitive results.
Stored procedure A has been granted execution for public user but inside application, it will prompt access denied message if particular user has no rights to use system although knew public user name and password. Because there is second layer of user validation inside system application.

However inside query analyzer, there is no way control execution of stored procedure A it as user knew the public user name and password.

Looking forward for replies from expert here. Thanks in advance.

Note: Hope my explaination here clearly describe my current problems.

View 4 Replies View Related

Locks

Nov 10, 2003

I have read that even during read procedures (sql select statements), sql server uses row locking. I know that you can use the NOLOCK keyword, but if you don't everytime that a user makes a selects statement on a table, does sql server really lock those rows, and if so are they then unavailable to another user who wants to make a select statement at the same time on that same table? That does not seem like it would be the case otherwise it would not scale well. Thanks for any clarification on this.

View 5 Replies View Related

Locks

Feb 13, 2002

I am using SQL Server7.0. I opened a table through the Enterprise Manager and left it open. In the Query Analyzer when I try to update a field on that table(more than 2000 rows), it goes on running. When I watched the Current Activity, it shows that the update process is being blocked by the select query. But if I try to update the same column for less than 1500 rows, there is no blocking issue and the update occurs immediately. Can anybody let me know why this is happening and what should I do to prevent it?

View 1 Replies View Related

Regarding Locks

Mar 28, 2000

I am using Sql Server 7.0

To I got the following error message. Can some one tell how to solve this issue.

Server: Msg 1204, Level 19, State 1, Procedure OPEN_OBJECTS, Line 2
SQL Server has run out of LOCKS. Rerun your statement when there are fewer active users, or ask the system administrator to reconfigure SQL Server with more LOCKS.

ranga.

View 1 Replies View Related

SQL Locks

Sep 26, 2000

what the best way to control locks, if inserting couple thousands records from one table to another.

View 5 Replies View Related

Locks

Feb 20, 2001

Hi
I have a big query which updates around 14000 rows at a time if i place a lock on the table and others try to update the same table is it possible to let them know that table is locked by someone else.

View 1 Replies View Related

Locks

Sep 19, 2002

2 quick questions :

1) How do I keep multiple users from editing the same record without locking the entire table? What would be a 'standard' way of handling this?

2) How do I keep 2 people from posting the same record?

Please help me understand locking, THANKS!!!

View 2 Replies View Related

Locks For A Max Value

Apr 11, 2005

I have a stored proc which will be entering/updating a record into a table. The table's key is an integer field which I may have to increment by one. I know I can use

declare @nextid int
set @netxid = max(id) from table
insert @nextid into table
Is some kind of lock the best way to approach this?

View 4 Replies View Related

Locks

Jun 15, 2004

Hello,

I have a problem in SQLSERVER 2000, when I execute a Query, the table get locked for insert or any other transaction, even for other queries.

Does SQL Server have a kind of lock mode different of Oracle ?

How do I solve this problem ??

View 5 Replies View Related

Locks

Jun 16, 2004

Hello There !!

I have a very big problem, with SQL SERVER 2000. I want to know about the locks with select.

When I execute a Select (so big), and I try to update or Insert into one of the tables that I invoke in the select, I get locked.

Is there in SQL Server, something like a Select for update, that could be causing the problem ???

Is there any way to select rows from a table without locking it ?

I really have a big problem with this, and I don't know so much about sql server !

Thank you so much !!!

View 14 Replies View Related

Locks

Jul 1, 2004

Some of my tables Were locked IN IS Mode.What does it mean?
Thanks.

View 2 Replies View Related

Locks

Jul 1, 2004

I Have 359 locks on MY Database ,They are always there on my DB.The DB is a development database and lots of summary Stored procedures will be running on this DB.
Does it effect the performance.How can I remove those locks.
Thanks.

View 2 Replies View Related

Locks

Jan 13, 2004

Hi All,
Sql server 7

pls provide me sql statement that shows all the locks. the goal is to produce the output on an HTML page.

pls help me in solving this

TIA
Adil

View 8 Replies View Related

Locks

Apr 18, 2007

Hi
I want to write all my select ststements using locks
how i should write a select ststement using locks
i searched for examples but iam not getting the syntax
Plz give me one example for select statements using locks

thanks in advance

Malathi Rao

View 4 Replies View Related

Locks

Jan 7, 2008



if have a transaction with statments like
begin
insert into table A ...where exists (select .. from table A)
transaction

What locks will be placed while inserting and selecting rows,if multiple users are accessing this concurrently

View 7 Replies View Related

Locks In SQL2000

Jun 1, 2007

How to lock a Row in SQL2000 so that nobody can select that row.
I applied ROWLOCK, but i am not finding the way.
My query is "SELECT * FROM tablename WITH (ROWLOCK)"
Is this the correct way to write locks.
I would be thankful if u help me

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved