PLSQL triggers

I am having some problems with my trigger..
Basically the trigger is there to inform the user that they are entering an item that already exists on the database.

The problem however is that the Trigger wont function, it will not allow any item to be entered into the database (even if this is the first item)
It does retrun a message if the same item exists on the database, but it still returns errors.
And even if I dont have the same thing on the database, the system returns a unique Constraint violation.
I am using a procedure to addnew items. But when I remove the trigger it functions perfectly so the error must be here.

I would really be gratefull to anyone who can help me understand this problem. To me the code is logical, obviously not to the system.

Heres the code:
-----
CREATE OR REPLACE TRIGGER UPDATEITEM
AFTER INSERT OR UPDATE OF title ON ITEM
DECLARE

CURSOR ist_item IS
select title from item where itemno = (select max(itemno) from item);

----Selects newest Item (the one that has been inserted

CURSOR sst_item IS
select itemno from item where itemno = (select max(itemno) from item);

v_count number;
old_count number;
num number;
new_title varchar(20);
old_title varchar(20);
errors_main EXCEPTION;
BEGIN
OPEN ist_item;
OPEN sst_item;
FETCH sst_item INTO num;

if num> 0 then ----checks if anything is in the record yet.

FETCH ist_item INTO new_title;

select title into old_title from item where title= new_title and itemno<> num; --looks at another item in the database with the same title -- --- but not the same number

if old_title=new_title THEN
RAISE_APPLICATION_ERROR (-20000, 'ITEM ALREADY EXISTS IN DATABASE');

END IF;
END IF;
END;
/
SHOW ERROR
-------Not sure what you are trying to accomplish.

1. Your trigger is fired after each STATEMENT, not after each ROW. You can insert/update millions rows into the ITEM table with a single statement. Wouldn't you like to check each insert/update ?

2. You are opening cursors, but not closing them.

3. As for as I can understand, you are assuming that the row with the highest ITEMNO is the one that you have been inserting/updating. Does that assumption really hold ?

To my point of view, forget about the trigger and just add a unique constraint on column TITLE in table ITEM. That is exactly what they are built for.

Good luck.|||What I am trying to achive is:
- Making sure that each item is unique, if someone is trying to put in another item of the same title, this trigger should find the existing item and update the quantity.
(At the moment there is an error message in place of the 'update table' statement) - I am trying to get the basics working.

So the trigger will look for items with the same name, then fire when it has found an existing item.

Thats what this statement does:
select title into old_title from item where title= new_title and itemno<> num;
Looks for a title that is the same as the new title that has been entered but is not the same record number.

The MAX(itemno) is to show the newest record. (The item is stored in a sequential fashion, thus the highest in the database will also be the newest entry and the one that needs to be compared with the rest of the database)

I know the Trigger looks a little sketchy, its just because I have been playing round with it so much, that some of the Close Cursor statement have been deleted, but even with them it doesnt work.

Big thanks for looking at this post, have you any further ideas...|||The MAX(itemno) is to show the newest record. (The item is stored in a sequential fashion, thus the highest in the database will also be the newest entry and the one that needs to be compared with the rest of the database)

This is a very tricky assumption you make. Did you think about updates ? Suppose your table contains two rows :
ItemNo Title
1 First_title
2 Second_title

Now suppose somebody updates row identified by ItemNo 1, and sets the title colum to "Second_Title". Does your trigger still do the job ? What about bulk inserts ? Did you notice that your trigger only fires after each statement. I can do a million inserts in your table and only have the trigger fired once...

You also probably will run into the notorious "ORA-04091 table is mutating".

The only proper way to enforce uniqueness is to use a constraint. But, if you still want to use a trigger, you might want to try (you need Oracle version 8.1.5 or higher):

create or replace trigger UPDATEITEM
AFTER INSERT OR UPDATE OF title ON ITEM
for each row -- verifies all inserted or updated rows !!
declare
pragma autonomous_transaction; -- circumvent ORA-04091
v_count number;
begin
select count(*) into v_count from item where title = :new_title;
if v_count > 0 then raise_application_error(-20000,'ITEM ALREADY EXISTS IN DATABASE'); end if;
end;|||Thats a really good point. (and I didnt consider the fact that it only fires once)

However: I think I can get away with it, in any case, because the entire concept of adding an item is done via procedures (it has to be, its part of the assignment) Therefore the issue of bulk loading never applies (as far as this scenario goes)
Although at a later stage I do want to make some compensations for bulk loading, but it is not really the highest priority.

The reason why this entire trigger looks show slip-shot is because the strength of the scenario that we are doing, realistically the system cannot be implemented in a real organisation, however it has to be implemented with the scenario in mind, and we cant make our own assumptions.

Thanks again for the reply, I am begining to understand how this is supposed to work.

PL-SQL to T-SQL trigger conv

Hi all,
I brought this up some time back, but I have a trigger that I was tasked,
begrudgingly, with converting from Oracle to SQLserver. After the comments
I received, which were great, it has me concerned that I'm trying to convert
line-for-line a trigger that might very well be poorly written in the first
place. Would any of you mind taking a look and giving me some feedback?
Unfortunately, I have no data tables to refer to myself, so can't provide
them to you.
I am going to attack the conversion in parts, and since I've already run
into some unfamiliar issues to deal with, I thought I'd ask before I go too
far down the road and find that the procedure has too many problems itself.
Trust me, I'd rather stick to my user interface code than have to deal with
converting a trigger. I'm only doing this to help the customer get through
where the vendor has given up.
CREATE OR REPLACE TRIGGER PartCost_WO AFTER INSERT ON PartTrans FOR EACH ROW
DECLARE
aNextVal NUMBER(9);
PricePer FLOAT;
BEGIN
IF (:new.HISTKEY) > 1 THEN
SELECT Z.NEXTVAL INTO aNextVal
FROM ZEQ_120 Z;
aNextVal := aNextVal + 1;
PricePer := :new.TRNCST/:new.PRTSISS;
INSERT INTO COSTPART
(USAGE, PRTKEY, STKKEY, TOTCOST, ADDDTTM, ADDBY, COSTKEY,
HISTKEY, CHRGDTTM, RATE)
VALUES
(:new.PRTSISS, :new.PRTKEY, :new.STKKEY, :new.TRNCST,
:new.ADDDTTM, 'PartCost_WO TRIGGER', aNextVal, :new.HISTKEY,
:new.ADDDTTM, PricePer);
UPDATE ZEQ_120 Z SET Z.NEXTVAL = Z.NEXTVAL + 1;
END IF;
END;
/
show errors trigger PartCost_WO;Mike
Have you looked at CREATE TRIGGER topic in the BOL?
CREATE TRIGGER PartCost_WO ON PartTrans FOR INSERT
DECLARE @.aNextVal DECIMAL or INT...
DECLARE @.PricePer FLOAT;
BEGIN
--Did not undesrtand what does it mean?
IF (:new.HISTKEY) > 1 THEN
SELECT Z.NEXTVAL INTO aNextVal
FROM ZEQ_120 Z;
SET @.aNextVal = @.aNextVal + 1;
SET @.PricePer := :new.TRNCST/:new.PRTSISS; --Where do these
columns/variables come from?
INSERT INTO COSTPART
(USAGE, PRTKEY, STKKEY, TOTCOST, ADDDTTM, ADDBY, COSTKEY,
HISTKEY, CHRGDTTM, RATE)
SELECT
new.PRTSISS, :new.PRTKEY, :new.STKKEY, :new.TRNCST,
:new.ADDDTTM, 'PartCost_WO TRIGGER', aNextVal, :new.HISTKEY,
:new.ADDDTTM, PricePer FROM SomeTable
UPDATE ZEQ_120 Z SET Z.NEXTVAL = Z.NEXTVAL + 1;--Don't you have a WHERE
condition here?
END;
"mikeb" <mike@.nohostanywhere.com> wrote in message
news:%23U5cDxwlFHA.3780@.tk2msftngp13.phx.gbl...
> Hi all,
> I brought this up some time back, but I have a trigger that I was tasked,
> begrudgingly, with converting from Oracle to SQLserver. After the
> comments I received, which were great, it has me concerned that I'm trying
> to convert line-for-line a trigger that might very well be poorly written
> in the first place. Would any of you mind taking a look and giving me
> some feedback? Unfortunately, I have no data tables to refer to myself, so
> can't provide them to you.
> I am going to attack the conversion in parts, and since I've already run
> into some unfamiliar issues to deal with, I thought I'd ask before I go
> too far down the road and find that the procedure has too many problems
> itself. Trust me, I'd rather stick to my user interface code than have to
> deal with converting a trigger. I'm only doing this to help the customer
> get through where the vendor has given up.
> CREATE OR REPLACE TRIGGER PartCost_WO AFTER INSERT ON PartTrans FOR EACH
> ROW
> DECLARE
> aNextVal NUMBER(9);
> PricePer FLOAT;
> BEGIN
> IF (:new.HISTKEY) > 1 THEN
> SELECT Z.NEXTVAL INTO aNextVal
> FROM ZEQ_120 Z;
> aNextVal := aNextVal + 1;
> PricePer := :new.TRNCST/:new.PRTSISS;
> INSERT INTO COSTPART
> (USAGE, PRTKEY, STKKEY, TOTCOST, ADDDTTM, ADDBY, COSTKEY,
> HISTKEY, CHRGDTTM, RATE)
> VALUES
> (:new.PRTSISS, :new.PRTKEY, :new.STKKEY, :new.TRNCST,
> :new.ADDDTTM, 'PartCost_WO TRIGGER', aNextVal, :new.HISTKEY,
> :new.ADDDTTM, PricePer);
> UPDATE ZEQ_120 Z SET Z.NEXTVAL = Z.NEXTVAL + 1;
> END IF;
> END;
> /
> show errors trigger PartCost_WO;
>
>
>|||Uri,
Here are some possible answers to your questions. Maybe you
can give it another shot - I'm reticent, because SQL Server doesn't
have FOR EACH ROW triggers, and it looks like this is trying to
emulate a home-grown identity-like column, which is tricky to do
all at once.
A FOR EACH ROW trigger will be executed once for each row
inserted, so what's needed here is a set-based query, a loop, or
a cursor.
The expression :new.<column> is probably the Oracle equivalent
of inserted.<column> for a row, so it can't be used in INSERT .. VALUES,
but you can do INSERT INTO .. SELECT .. FROM inserted.
aNextVal is a variable, so SELECT Z.NEXTVAL INTO aNextVal
is probably SET @.aNextVal = (SELECT Z.NEXTVAL FROM ZEQ_120 Z)
It looks very much like ZEQ_120 contains only one row, and that row
is the last home-grown identity value used. This would explain SELECT INTO
a variable, as well as the UPDATE of ZEQ_120 with no WHERE clause.
The hardest part of this is to get the incrementing column values that
go into
COSTPART correct. The key column of PartTrans would be helpful
to know here.
If this trigger will only be called for single-row inserts, then it's
easier, and
IF @.@.rowcount > 1 BEGIN <report error> <rollback transaction> END
can help out.
Steve Kass
Drew University
Uri Dimant wrote:

>Mike
>Have you looked at CREATE TRIGGER topic in the BOL?
>CREATE TRIGGER PartCost_WO ON PartTrans FOR INSERT
>DECLARE @.aNextVal DECIMAL or INT...
>DECLARE @.PricePer FLOAT;
>BEGIN
>--Did not undesrtand what does it mean?
> IF (:new.HISTKEY) > 1 THEN
> SELECT Z.NEXTVAL INTO aNextVal
> FROM ZEQ_120 Z;
>
> SET @.aNextVal = @.aNextVal + 1;
> SET @.PricePer := :new.TRNCST/:new.PRTSISS; --Where do these
>columns/variables come from?
> INSERT INTO COSTPART
> (USAGE, PRTKEY, STKKEY, TOTCOST, ADDDTTM, ADDBY, COSTKEY,
> HISTKEY, CHRGDTTM, RATE)
>SELECT
> new.PRTSISS, :new.PRTKEY, :new.STKKEY, :new.TRNCST,
>:new.ADDDTTM, 'PartCost_WO TRIGGER', aNextVal, :new.HISTKEY,
>:new.ADDDTTM, PricePer FROM SomeTable
>
>UPDATE ZEQ_120 Z SET Z.NEXTVAL = Z.NEXTVAL + 1;--Don't you have a WHERE
>condition here?
>
>END;
>"mikeb" <mike@.nohostanywhere.com> wrote in message
>news:%23U5cDxwlFHA.3780@.tk2msftngp13.phx.gbl...
>
>
>|||On Mon, 1 Aug 2005 19:45:23 -0700, mikeb wrote:
(snip)
>Trust me, I'd rather stick to my user interface code than have to deal with
>converting a trigger. I'm only doing this to help the customer get through
>where the vendor has given up.
Hi mikeb,
Okay. With the help of Uri and Steve, I think I can help you help the
customer get through <g>.
I agree with Steve: the ZEQ_120 table must contain just one row that
holds a "last used" value. This code really wouldn't make sense if it
were otherwise.
The easiest way to port this to SQL Server is to change the COSTKEY
column in the COSTPART table to an IDENTITY column. That way, SQL Server
will dish out a new, continuously increasing number. Without the need
for a seperate one-row "last number used" table, and without the locking
issues, deadlock opportunities and scalability restrictions that such a
table incurs.
Note however that the IDENTITY column does not guarantee that there are
no gaps. If a transaction aborts and is rolled back, the IDENTITY values
used in that transaction won't be reused. The Oracle code you posted
will reuse those values, since the change to the ZEQ_120 table will also
be rolled back.
If you can change COSTPART.COSTKEY to an identity column, the equivalent
trigger in SQL server becomes very easy:
CREATE TRIGGER PartCost_WO
ON PartTrans
AFTER INSERT
AS
INSERT INTO COSTPART
(USAGE, PRTKEY, STKKEY, TOTCOST, ADDDTTM, ADDBY,
HISTKEY, CHRGDTTM, RATE)
SELECT PRTSISS, PRTKEY, STKKEY, TRNCST, ADDTTM, 'PartCost_WO TRIGGER',
HISTKEY, ADDDTTM, TRNCST/PRTSISS
FROM inserted
go
Note that this changes the logic from a row-based trigger to a set-based
trigger (the only trigger type supported in SQL Server). Probably a lot
faster too.
Also, it might make sense to redefine COSTPART.RATE as a computed
column. Storing a value that can be computed from two other values in
the same row only makes sense if there's the possiblity that one of the
values might change at a later time without the others changing along.
But if RATE always has to be equal to TPRTKEY/USAGE, then it's better to
use a computed column.
Final advice: don't use all caps. Your code will be much eaasier to read
and understand if you use ALL CAPS for keywords, and PascalCase for
table and column names.
CREATE TABLE CostPart
(CostKey int NOT NULL IDENTITY,
-- other columns,
Rate AS PrtKey / Usage,
PRIMARY KEY (...),
-- other constraints,
)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks Hugo.
I couldn't agree with you more on all points - even down to the all caps and
confusing column names (oh wait, maybe that was my own peve).
Unfortunately, I can't change anything about the tables other than adding in
a trigger. This database was written for another vendors software, I'm only
augmenting it to make it work the way it should.
And you guys are right, the zeq_# table is used to gain a unique value for a
specific inserted column of data in a row. Again, I'd do this differently,
probably even without use of an identity column.
That aside, I appreciate all the comments and continued input! I'm just
very unfamiliar with oracle sql and only at the tip of the 'berg of t-sql
(as I continuously humble myself when reading through this NG).
Thanks again!
btw, here's how I converted the trigger so far -- no database yet in my
hands to be able to test it - but can I get some input? Would be very nice
to be able to insert it into a test database and have it work right from the
start!! But I don't know it enough to really test the logic against the
oracle code.
Create Trigger WO_PrtCost ON PRTTRNI After Insert
As
Declare @.aNextVal int
Declare @.PricePer float
Begin
If (Inserted.HISTKEY > 1)
Begin
SELECT @.aNextVal = Z.NEXTVAL FROM ZEQ_120 Z
Select @.aNextVal = @.aNextVal + 1
Select @.PricePer = Inserted.TRNCST/Inserted.PRTSISS
Insert COSTPART
(
USAGE,
PRTKEY,
STKKEY,
TOTCOST,
ADDDTTM,
ADDBY,
COSTKEY,
HISTKEY,
CHRGDTTM,
RATE
)
Select
PRTSISS,
PRTKEY,
STKKEY,
TRNCST,
ADDDTTM,
'WO_PRTCOST TRIGGER',
@.aNextVal,
HISTKEY,
ADDDTTM,
PricePer
From
Inserted
Update ZEQ_120
Set NEXTVAL = NEXTVAL + 1
End
End
--RaiseError('trigger WO_PRTCOST', 1, 1)
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:fgv4f1tf6q28otl42arm8q488m24ht2so2@.
4ax.com...
> On Mon, 1 Aug 2005 19:45:23 -0700, mikeb wrote:
> (snip)
> Hi mikeb,
> Okay. With the help of Uri and Steve, I think I can help you help the
> customer get through <g>.
> I agree with Steve: the ZEQ_120 table must contain just one row that
> holds a "last used" value. This code really wouldn't make sense if it
> were otherwise.
> The easiest way to port this to SQL Server is to change the COSTKEY
> column in the COSTPART table to an IDENTITY column. That way, SQL Server
> will dish out a new, continuously increasing number. Without the need
> for a seperate one-row "last number used" table, and without the locking
> issues, deadlock opportunities and scalability restrictions that such a
> table incurs.
> Note however that the IDENTITY column does not guarantee that there are
> no gaps. If a transaction aborts and is rolled back, the IDENTITY values
> used in that transaction won't be reused. The Oracle code you posted
> will reuse those values, since the change to the ZEQ_120 table will also
> be rolled back.
> If you can change COSTPART.COSTKEY to an identity column, the equivalent
> trigger in SQL server becomes very easy:
> CREATE TRIGGER PartCost_WO
> ON PartTrans
> AFTER INSERT
> AS
> INSERT INTO COSTPART
> (USAGE, PRTKEY, STKKEY, TOTCOST, ADDDTTM, ADDBY,
> HISTKEY, CHRGDTTM, RATE)
> SELECT PRTSISS, PRTKEY, STKKEY, TRNCST, ADDTTM, 'PartCost_WO TRIGGER',
> HISTKEY, ADDDTTM, TRNCST/PRTSISS
> FROM inserted
> go
> Note that this changes the logic from a row-based trigger to a set-based
> trigger (the only trigger type supported in SQL Server). Probably a lot
> faster too.
> Also, it might make sense to redefine COSTPART.RATE as a computed
> column. Storing a value that can be computed from two other values in
> the same row only makes sense if there's the possiblity that one of the
> values might change at a later time without the others changing along.
> But if RATE always has to be equal to TPRTKEY/USAGE, then it's better to
> use a computed column.
> Final advice: don't use all caps. Your code will be much eaasier to read
> and understand if you use ALL CAPS for keywords, and PascalCase for
> table and column names.
> CREATE TABLE CostPart
> (CostKey int NOT NULL IDENTITY,
> -- other columns,
> Rate AS PrtKey / Usage,
> PRIMARY KEY (...),
> -- other constraints,
> )
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||No need for further review - thanks for the help all!
"mikeb" <mike@.nohostanywhere.com> wrote in message
news:u$%23%23s$UmFHA.1412@.TK2MSFTNGP09.phx.gbl...
> Thanks Hugo.
> I couldn't agree with you more on all points - even down to the all caps
> and confusing column names (oh wait, maybe that was my own peve).
> Unfortunately, I can't change anything about the tables other than adding
> in a trigger. This database was written for another vendors software, I'm
> only augmenting it to make it work the way it should.
> And you guys are right, the zeq_# table is used to gain a unique value for
> a specific inserted column of data in a row. Again, I'd do this
> differently, probably even without use of an identity column.
> That aside, I appreciate all the comments and continued input! I'm just
> very unfamiliar with oracle sql and only at the tip of the 'berg of t-sql
> (as I continuously humble myself when reading through this NG).
> Thanks again!
> btw, here's how I converted the trigger so far -- no database yet in my
> hands to be able to test it - but can I get some input? Would be very
> nice to be able to insert it into a test database and have it work right
> from the start!! But I don't know it enough to really test the logic
> against the oracle code.
> Create Trigger WO_PrtCost ON PRTTRNI After Insert
> As
> Declare @.aNextVal int
> Declare @.PricePer float
> Begin
> If (Inserted.HISTKEY > 1)
> Begin
> SELECT @.aNextVal = Z.NEXTVAL FROM ZEQ_120 Z
> Select @.aNextVal = @.aNextVal + 1
> Select @.PricePer = Inserted.TRNCST/Inserted.PRTSISS
> Insert COSTPART
> (
> USAGE,
> PRTKEY,
> STKKEY,
> TOTCOST,
> ADDDTTM,
> ADDBY,
> COSTKEY,
> HISTKEY,
> CHRGDTTM,
> RATE
> )
> Select
> PRTSISS,
> PRTKEY,
> STKKEY,
> TRNCST,
> ADDDTTM,
> 'WO_PRTCOST TRIGGER',
> @.aNextVal,
> HISTKEY,
> ADDDTTM,
> PricePer
> From
> Inserted
> Update ZEQ_120
> Set NEXTVAL = NEXTVAL + 1
> End
> End
> --RaiseError('trigger WO_PRTCOST', 1, 1)
>
> "Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
> news:fgv4f1tf6q28otl42arm8q488m24ht2so2@.
4ax.com...
>

Please, a SQL trigger question!

Hi! I have no experience at all using triggers, and I don't even know
very well SQL server. I hope someone can help me with the folling: I
need to know if it is possible to implement a trigger that monitors
the value of an specific field, and when it changes the value from 0
to 1 it should update the value of another field in another table but
after some arithmetic operations. The arithmetics operations involves
data within the same database.

Is this can be made using triggers and stored procedures?Of course it can!

Look for this key words in SQL BOL: triggers, after, 'if update', inserted tables, deleted tables.

--
Dean Savovic
www.teched.hr

"Alejandro" <alejandro_ceja@.yahoo.com.mx> wrote in message news:85729932.0311041500.64581e86@.posting.google.c om...
> Hi! I have no experience at all using triggers, and I don't even know
> very well SQL server. I hope someone can help me with the folling: I
> need to know if it is possible to implement a trigger that monitors
> the value of an specific field, and when it changes the value from 0
> to 1 it should update the value of another field in another table but
> after some arithmetic operations. The arithmetics operations involves
> data within the same database.
> Is this can be made using triggers and stored procedures?|||Yes it can.

Personally Id recommend against it as I believe triggers to be the
very personal work of satan.

They can be quite slow - specially on heavy load tables, and have a
bad habit of hiding away and getting lost when you move the
database...

But yes - sure you can do that.

alejandro_ceja@.yahoo.com.mx (Alejandro) wrote in message news:<85729932.0311041500.64581e86@.posting.google.com>...

> Is this can be made using triggers and stored procedures?|||Hi

SQL server does not have the ability to create a trigger on an
individual column
but you can check that a column has changed within a trigger but
either comparing the values for that column in the inserted and
deleted or possibly the COLUMNS_UPDATED clause (Although a column can
be updated it may not be to a different value!).

See the "CREATE TRIGGER" topic in Books Online or at:
http://msdn.microsoft.com/library/d...reate2_7eeq.asp

The examples given in this topic also who how to update other tables.

HTH

John

alejandro_ceja@.yahoo.com.mx (Alejandro) wrote in message news:<85729932.0311041500.64581e86@.posting.google.com>...
> Hi! I have no experience at all using triggers, and I don't even know
> very well SQL server. I hope someone can help me with the folling: I
> need to know if it is possible to implement a trigger that monitors
> the value of an specific field, and when it changes the value from 0
> to 1 it should update the value of another field in another table but
> after some arithmetic operations. The arithmetics operations involves
> data within the same database.
> Is this can be made using triggers and stored procedures?

please tell me how to know when a deadlock occurs in sp

can i write a trigger which tells me when a deadlock occursDo it on the client
The error number is 1204 if I remember well.
"raghu veer" <raghuveer@.discussions.microsoft.com> wrote in message
news:3EEECEDC-C8D4-4F76-8C44-AE290DF52D95@.microsoft.com...
> can i write a trigger which tells me when a deadlock occurs|||Raghu
when ever deadlock occurs it throughs an error no :1205
so you can have a mechanism to set something like this for further
investigation through trigger.
IF @.@.ERROR = 1205
begin
-- EITHER NOTIFY OR INSERT INTO A TEMP TABLE
--RUN PROCEDURE AGAIN TO DO THE WORK.
END
Regards
R.D
"raghu veer" wrote:

> can i write a trigger which tells me when a deadlock occurs|||Uri
1204 is used to trace TRACEFLAG(1204)
while error is 1205, I think
Regards
R.D
"Uri Dimant" wrote:

> Do it on the client
> The error number is 1204 if I remember well.
>
> "raghu veer" <raghuveer@.discussions.microsoft.com> wrote in message
> news:3EEECEDC-C8D4-4F76-8C44-AE290DF52D95@.microsoft.com...
>
>|||When a transaction is chosen as the deadlock victim, that transaction is
rolled back and a 1205 error is returned on the connection. The batch may
or may not be terminated, so you should add error handling code after every
statement in a transaction. If a rollback occurs within a trigger, that
trigger continues executing the balance of its body and then the batch is
terminated. This means that error handling is needed within the body of a
trigger as well. No additional triggers are fired and no statements
following the statement that caused the trigger to fire execute. Because
the batch may also be terminated by a 1205 error, you must add code to
detect it in the client app.
Error handling code must exist in both the stored procedure and the client
app. This is extremely important, because if you fail to check for errors
after every statement within a transaction, the statements following the
error will execute in autocommit mode, which can introduce inconsistency
into the database. In addition, if you have multiple data modification
statements within a trigger, you must also add error handling code after
each statement in the trigger. This cannot be stressed enough. Tracking
down the resultant data corruption is extremely difficult to do.
It is not possible to write a trigger that will detect all deadlocks and
inform you when they occur. The trigger containing the detection and
notification code may not fire if the deadlock occurs while another trigger
on the same table is executing.
"raghu veer" <raghuveer@.discussions.microsoft.com> wrote in message
news:3EEECEDC-C8D4-4F76-8C44-AE290DF52D95@.microsoft.com...
> can i write a trigger which tells me when a deadlock occurs|||Raghu
Follow these steps in dev node.
1)DBCC TRACEON(3604)
2)DBCC TRACEON(1204)
3) RUN SPs that are raising deadlocks
4) you can analyse the results and find out which object is becoming dead
lock victim and even which line of sp is causing deadlock
5) attack query
If you have problem in analysing and finding the object Post results here
Regards
R.D
"Brian Selzer" wrote:
> When a transaction is chosen as the deadlock victim, that transaction is
> rolled back and a 1205 error is returned on the connection. The batch may
> or may not be terminated, so you should add error handling code after ever
y
> statement in a transaction. If a rollback occurs within a trigger, that
> trigger continues executing the balance of its body and then the batch is
> terminated. This means that error handling is needed within the body of a
> trigger as well. No additional triggers are fired and no statements
> following the statement that caused the trigger to fire execute. Because
> the batch may also be terminated by a 1205 error, you must add code to
> detect it in the client app.
> Error handling code must exist in both the stored procedure and the client
> app. This is extremely important, because if you fail to check for errors
> after every statement within a transaction, the statements following the
> error will execute in autocommit mode, which can introduce inconsistency
> into the database. In addition, if you have multiple data modification
> statements within a trigger, you must also add error handling code after
> each statement in the trigger. This cannot be stressed enough. Tracking
> down the resultant data corruption is extremely difficult to do.
> It is not possible to write a trigger that will detect all deadlocks and
> inform you when they occur. The trigger containing the detection and
> notification code may not fire if the deadlock occurs while another trigge
r
> on the same table is executing.
> "raghu veer" <raghuveer@.discussions.microsoft.com> wrote in message
> news:3EEECEDC-C8D4-4F76-8C44-AE290DF52D95@.microsoft.com...
>
>|||i executed both now there is no errror
tomorrow i will execute them and let u know
thankssssssssssssssssssssssssssss
"R.D" wrote:
> Raghu
> Follow these steps in dev node.
> 1)DBCC TRACEON(3604)
> 2)DBCC TRACEON(1204)
> 3) RUN SPs that are raising deadlocks
> 4) you can analyse the results and find out which object is becoming dead
> lock victim and even which line of sp is causing deadlock
> 5) attack query
> If you have problem in analysing and finding the object Post results here
> Regards
> R.D
>
> "Brian Selzer" wrote:

pLEASE HELP:URGENT please

I am new to sql server . I have to write a trigger to insert the data into
second table when there is any update in first table. Do I have to execute
the trigger every time by some command or it automatically fires when first
table gets updated?
Thanks in advance
NikiIt automatically fires. Here's a rough example:
create trigger tru_MyTable on MyTable
after update
as
if @.@.ROWCOUNT = 0
return
insert MyOtherTable
select
*
from
inserted
go
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"nikila" <nikilav@.yahoo.com> wrote in message
news:O%232LMtoVFHA.1384@.TK2MSFTNGP09.phx.gbl...
I am new to sql server . I have to write a trigger to insert the data into
second table when there is any update in first table. Do I have to execute
the trigger every time by some command or it automatically fires when first
table gets updated?
Thanks in advance
Niki|||Hi Nikila
The difference between Trigger and Stored Procedure is,
You need to execute a stored procedure explicitly, to run the stored procedu
re
There is no way where you can fire a Trigger from outside. The Trigger is
fired when an operation is performed on a table.
U need a "FOR UPDATE" trigger in your situation
Here is how you do it:
CREATE TRIGGER <TRIGGER_NAME>
ON <TABLE-1>
FOR UPDATE AS
INSERT INTO TABLE-2 SELECT * FROM TABLE-1
GO
For more information about triggers, you can refer:
http://msdn.microsoft.com/library/d... />
2_7eeq.asp
best Regards,
Chandra
http://chanduas.blogspot.com/
---
"nikila" wrote:

> I am new to sql server . I have to write a trigger to insert the data int
o
> second table when there is any update in first table. Do I have to execute
> the trigger every time by some command or it automatically fires when firs
t
> table gets updated?
> Thanks in advance
> Niki
>
>|||Uh, this trigger would insert the entire contents of TABLE-1 into TABLE-2
every time TABLE-1 was updated.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:A4F71FF9-CD9F-45B7-A95F-C728E89B156D@.microsoft.com...
Hi Nikila
The difference between Trigger and Stored Procedure is,
You need to execute a stored procedure explicitly, to run the stored
procedure
There is no way where you can fire a Trigger from outside. The Trigger is
fired when an operation is performed on a table.
U need a "FOR UPDATE" trigger in your situation
Here is how you do it:
CREATE TRIGGER <TRIGGER_NAME>
ON <TABLE-1>
FOR UPDATE AS
INSERT INTO TABLE-2 SELECT * FROM TABLE-1
GO
For more information about triggers, you can refer:
http://msdn.microsoft.com/library/d... />
2_7eeq.asp
best Regards,
Chandra
http://chanduas.blogspot.com/
---
"nikila" wrote:

> I am new to sql server . I have to write a trigger to insert the data
> into
> second table when there is any update in first table. Do I have to execute
> the trigger every time by some command or it automatically fires when
> first
> table gets updated?
> Thanks in advance
> Niki
>
>

pLEASE HELP:URGENT please

I am new to sql server . I have to write a trigger to insert the data into
second table when there is any update in first table. Do I have to execute
the trigger every time by some command or it automatically fires when first
table gets updated?
Thanks in advance
NikiIt automatically fires. Here's a rough example:
create trigger tru_MyTable on MyTable
after update
as
if @.@.ROWCOUNT = 0
return
insert MyOtherTable
select
*
from
inserted
go
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"nikila" <nikilav@.yahoo.com> wrote in message
news:O%232LMtoVFHA.1384@.TK2MSFTNGP09.phx.gbl...
I am new to sql server . I have to write a trigger to insert the data into
second table when there is any update in first table. Do I have to execute
the trigger every time by some command or it automatically fires when first
table gets updated?
Thanks in advance
Niki|||Hi Nikila
The difference between Trigger and Stored Procedure is,
You need to execute a stored procedure explicitly, to run the stored procedure
There is no way where you can fire a Trigger from outside. The Trigger is
fired when an operation is performed on a table.
U need a "FOR UPDATE" trigger in your situation
Here is how you do it:
CREATE TRIGGER <TRIGGER_NAME>
ON <TABLE-1>
FOR UPDATE AS
INSERT INTO TABLE-2 SELECT * FROM TABLE-1
GO
For more information about triggers, you can refer
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_7eeq.asp
best Regards,
Chandra
http://chanduas.blogspot.com/
---
"nikila" wrote:
> I am new to sql server . I have to write a trigger to insert the data into
> second table when there is any update in first table. Do I have to execute
> the trigger every time by some command or it automatically fires when first
> table gets updated?
> Thanks in advance
> Niki
>
>|||Uh, this trigger would insert the entire contents of TABLE-1 into TABLE-2
every time TABLE-1 was updated.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:A4F71FF9-CD9F-45B7-A95F-C728E89B156D@.microsoft.com...
Hi Nikila
The difference between Trigger and Stored Procedure is,
You need to execute a stored procedure explicitly, to run the stored
procedure
There is no way where you can fire a Trigger from outside. The Trigger is
fired when an operation is performed on a table.
U need a "FOR UPDATE" trigger in your situation
Here is how you do it:
CREATE TRIGGER <TRIGGER_NAME>
ON <TABLE-1>
FOR UPDATE AS
INSERT INTO TABLE-2 SELECT * FROM TABLE-1
GO
For more information about triggers, you can refer:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_7eeq.asp
best Regards,
Chandra
http://chanduas.blogspot.com/
---
"nikila" wrote:
> I am new to sql server . I have to write a trigger to insert the data
> into
> second table when there is any update in first table. Do I have to execute
> the trigger every time by some command or it automatically fires when
> first
> table gets updated?
> Thanks in advance
> Niki
>
>

pLEASE HELP:URGENT please

I am new to sql server . I have to write a trigger to insert the data into
second table when there is any update in first table. Do I have to execute
the trigger every time by some command or it automatically fires when first
table gets updated?
Thanks in advance
Niki
It automatically fires. Here's a rough example:
create trigger tru_MyTable on MyTable
after update
as
if @.@.ROWCOUNT = 0
return
insert MyOtherTable
select
*
from
inserted
go
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"nikila" <nikilav@.yahoo.com> wrote in message
news:O%232LMtoVFHA.1384@.TK2MSFTNGP09.phx.gbl...
I am new to sql server . I have to write a trigger to insert the data into
second table when there is any update in first table. Do I have to execute
the trigger every time by some command or it automatically fires when first
table gets updated?
Thanks in advance
Niki
|||Hi Nikila
The difference between Trigger and Stored Procedure is,
You need to execute a stored procedure explicitly, to run the stored procedure
There is no way where you can fire a Trigger from outside. The Trigger is
fired when an operation is performed on a table.
U need a "FOR UPDATE" trigger in your situation
Here is how you do it:
CREATE TRIGGER <TRIGGER_NAME>
ON <TABLE-1>
FOR UPDATE AS
INSERT INTO TABLE-2 SELECT * FROM TABLE-1
GO
For more information about triggers, you can refer:
http://msdn.microsoft.com/library/de...eate2_7eeq.asp
best Regards,
Chandra
http://chanduas.blogspot.com/
"nikila" wrote:

> I am new to sql server . I have to write a trigger to insert the data into
> second table when there is any update in first table. Do I have to execute
> the trigger every time by some command or it automatically fires when first
> table gets updated?
> Thanks in advance
> Niki
>
>
|||Uh, this trigger would insert the entire contents of TABLE-1 into TABLE-2
every time TABLE-1 was updated.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:A4F71FF9-CD9F-45B7-A95F-C728E89B156D@.microsoft.com...
Hi Nikila
The difference between Trigger and Stored Procedure is,
You need to execute a stored procedure explicitly, to run the stored
procedure
There is no way where you can fire a Trigger from outside. The Trigger is
fired when an operation is performed on a table.
U need a "FOR UPDATE" trigger in your situation
Here is how you do it:
CREATE TRIGGER <TRIGGER_NAME>
ON <TABLE-1>
FOR UPDATE AS
INSERT INTO TABLE-2 SELECT * FROM TABLE-1
GO
For more information about triggers, you can refer:
http://msdn.microsoft.com/library/de...eate2_7eeq.asp
best Regards,
Chandra
http://chanduas.blogspot.com/
"nikila" wrote:

> I am new to sql server . I have to write a trigger to insert the data
> into
> second table when there is any update in first table. Do I have to execute
> the trigger every time by some command or it automatically fires when
> first
> table gets updated?
> Thanks in advance
> Niki
>
>

Please help: Views and INSTEAD OF UPDATE trigger

Hi:

Currently I have two tables T1 and T2. A view V1 is defined over T1 with an INSTEAD OF UPDATE trigger and another view V2 is defined over V1 and T2 with another INSTEAD OF UPDATE trigger. Unfortunately, inside V2s trigger following update statement is not working:
Update V1 set V1.name = i.name from inserted i
because SQL server complains:
View 'V1' has an INSTEAD OF UPDATE trigger and cannot be a target of an UPDATE FROM statement.
Is there any way to get around this problem? I.e., how can I make the INSTEAD OF UPDATE trigger in V2 to work if I cant reference inserted?

Your help is appreciated,

JeffI think the problem is not with your "inserted" table, but with the target of the insert (View V1).

Modify your INSTEAD OF triggers so that they reference the underlying tables directly, rather than through secondary views.