RI Question: Declarative vs Trigger

I am using SQL Server 7, 2000 and 2005.
All of my cascading RI is handled via triggers (generated by data modeller).
I only use triggers for RI and they implement simple cascading or setting
null.
From a performance point of view (100 million plus recs in a table), is it
better to use declarative RI or trigger RI?
The reason I ask is because I currently still support SQL 7 and I would like
to avoid separate app versions of the database per SQL Server version. But
if performance is better, I will consider separate app db versions.
Isaac Alexander wrote:
> I am using SQL Server 7, 2000 and 2005.
> All of my cascading RI is handled via triggers (generated by data modeller).
> I only use triggers for RI and they implement simple cascading or setting
> null.
> From a performance point of view (100 million plus recs in a table), is it
> better to use declarative RI or trigger RI?
> The reason I ask is because I currently still support SQL 7 and I would like
> to avoid separate app versions of the database per SQL Server version. But
> if performance is better, I will consider separate app db versions.
Isaac,
Usually RI performs much better than triggers. Also be aware that in
some cases triggers just do not fire, for instance
- TRUNCATE TABLE does not fire delete triggers
- nested and recursive triggers settings may cause triggers to not
fire
- triggers that work under READ COMMITTED isolation level may silently
fail under READ COMMITTED SNAPSHOT
So triggers are not 100% watertight.
On top of that, the optimizer may use RI as additional information and
come up with a better plan - it does not look into triggers.
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/
|||"Alex Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1165947132.769112.294310@.f1g2000cwa.googlegro ups.com...
> Isaac Alexander wrote:
> Isaac,
> Usually RI performs much better than triggers. Also be aware that in
> some cases triggers just do not fire, for instance
> - TRUNCATE TABLE does not fire delete triggers
> - nested and recursive triggers settings may cause triggers to not
> fire
> - triggers that work under READ COMMITTED isolation level may silently
> fail under READ COMMITTED SNAPSHOT
> So triggers are not 100% watertight.
> On top of that, the optimizer may use RI as additional information and
> come up with a better plan - it does not look into triggers.
>
Thanks. I was hoping that I got this answer.

RI Question: Declarative vs Trigger

I am using SQL Server 7, 2000 and 2005.
All of my cascading RI is handled via triggers (generated by data modeller).
I only use triggers for RI and they implement simple cascading or setting
null.
From a performance point of view (100 million plus recs in a table), is it
better to use declarative RI or trigger RI?
The reason I ask is because I currently still support SQL 7 and I would like
to avoid separate app versions of the database per SQL Server version. But
if performance is better, I will consider separate app db versions.Isaac Alexander wrote:
> I am using SQL Server 7, 2000 and 2005.
> All of my cascading RI is handled via triggers (generated by data modeller).
> I only use triggers for RI and they implement simple cascading or setting
> null.
> From a performance point of view (100 million plus recs in a table), is it
> better to use declarative RI or trigger RI?
> The reason I ask is because I currently still support SQL 7 and I would like
> to avoid separate app versions of the database per SQL Server version. But
> if performance is better, I will consider separate app db versions.
Isaac,
Usually RI performs much better than triggers. Also be aware that in
some cases triggers just do not fire, for instance
- TRUNCATE TABLE does not fire delete triggers
- nested and recursive triggers settings may cause triggers to not
fire
- triggers that work under READ COMMITTED isolation level may silently
fail under READ COMMITTED SNAPSHOT
So triggers are not 100% watertight.
On top of that, the optimizer may use RI as additional information and
come up with a better plan - it does not look into triggers.
--
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/|||"Alex Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1165947132.769112.294310@.f1g2000cwa.googlegroups.com...
> Isaac Alexander wrote:
>> I am using SQL Server 7, 2000 and 2005.
>> All of my cascading RI is handled via triggers (generated by data
>> modeller).
>> I only use triggers for RI and they implement simple cascading or setting
>> null.
>> From a performance point of view (100 million plus recs in a table), is
>> it
>> better to use declarative RI or trigger RI?
>> The reason I ask is because I currently still support SQL 7 and I would
>> like
>> to avoid separate app versions of the database per SQL Server version.
>> But
>> if performance is better, I will consider separate app db versions.
> Isaac,
> Usually RI performs much better than triggers. Also be aware that in
> some cases triggers just do not fire, for instance
> - TRUNCATE TABLE does not fire delete triggers
> - nested and recursive triggers settings may cause triggers to not
> fire
> - triggers that work under READ COMMITTED isolation level may silently
> fail under READ COMMITTED SNAPSHOT
> So triggers are not 100% watertight.
> On top of that, the optimizer may use RI as additional information and
> come up with a better plan - it does not look into triggers.
>
Thanks. I was hoping that I got this answer.

RI Question: Declarative vs Trigger

I am using SQL Server 7, 2000 and 2005.
All of my cascading RI is handled via triggers (generated by data modeller).
I only use triggers for RI and they implement simple cascading or setting
null.
From a performance point of view (100 million plus recs in a table), is it
better to use declarative RI or trigger RI?
The reason I ask is because I currently still support SQL 7 and I would like
to avoid separate app versions of the database per SQL Server version. But
if performance is better, I will consider separate app db versions.Isaac Alexander wrote:
> I am using SQL Server 7, 2000 and 2005.
> All of my cascading RI is handled via triggers (generated by data modeller
).
> I only use triggers for RI and they implement simple cascading or setting
> null.
> From a performance point of view (100 million plus recs in a table), is it
> better to use declarative RI or trigger RI?
> The reason I ask is because I currently still support SQL 7 and I would li
ke
> to avoid separate app versions of the database per SQL Server version. But
> if performance is better, I will consider separate app db versions.
Isaac,
Usually RI performs much better than triggers. Also be aware that in
some cases triggers just do not fire, for instance
- TRUNCATE TABLE does not fire delete triggers
- nested and recursive triggers settings may cause triggers to not
fire
- triggers that work under READ COMMITTED isolation level may silently
fail under READ COMMITTED SNAPSHOT
So triggers are not 100% watertight.
On top of that, the optimizer may use RI as additional information and
come up with a better plan - it does not look into triggers.
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/|||"Alex Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1165947132.769112.294310@.f1g2000cwa.googlegroups.com...
> Isaac Alexander wrote:
> Isaac,
> Usually RI performs much better than triggers. Also be aware that in
> some cases triggers just do not fire, for instance
> - TRUNCATE TABLE does not fire delete triggers
> - nested and recursive triggers settings may cause triggers to not
> fire
> - triggers that work under READ COMMITTED isolation level may silently
> fail under READ COMMITTED SNAPSHOT
> So triggers are not 100% watertight.
> On top of that, the optimizer may use RI as additional information and
> come up with a better plan - it does not look into triggers.
>
Thanks. I was hoping that I got this answer.

RFC: Trigger uodating records in INSERTED Table

1) We have a trigger which applies further changes to the inserted/updated
records. Is this fundamentally bad or an acceptable practice?
2) We suspect that one version of such a trigger is causing deadlocks.
Interestingly this does not seem to happen if we use a cursor. See two
versions below. Any insights why the behavior differs?
Looking forward to your comments,
Jonathan Orgel
-- Suspected of causing dead lock
CREATE TRIGGER IU_DOCUMENTS ON DOCUMENTS
FOR INSERT, UPDATE
AS
BEGIN
UPDATE DOCUMENTS SET X=Y WHERE DOCUMENTID IN (SELECT DOCUMENTID FROM
INSERTED)
END
-- OK...
CREATE TRIGGER IU_DOCUMENTS ON DOCUMENTS
FOR INSERT, UPDATE
AS
BEGIN
DECLARE IndexCursor CURSOR LOCAL STATIC FOR SELECT DOCUMENTID FROM
INSERTED
OPEN IndexCursor
FETCH NEXT FROM IndexCursor INTO @.DOCUMENTID
WHILE @.@.FETCH_STATUS = 0
BEGIN
UPDATE DOCUMENTS SET X=Y WHERE DOCUMENTID = @.DOCUMENTID
FETCH NEXT FROM IndexCursor INTO @.DOCUMENTID
END
CLOSE IndexCursor
DEALLOCATE IndexCursor
ENDRewrite your first trigger to be like the following
CREATE TRIGGER IU_DOCUMENTS ON DOCUMENTS
FOR INSERT, UPDATE
AS
BEGIN
UPDATE
a
SET
X=Y
from
documents a inner join inserted b on
a.documentid=b.documentid
END
I think you are getting deadlocks because of "where documentid in ..."
syntax. Usually this causes SQL Server not to use index optimisation, and
would attempt to do a table scan. Since your code is also doing an update on
the same table, this would cause deadlocks. Also make sure you have an index
on documentid. By the look of things, documentid should be the primary key
and should obviously have been indexed to start with.
HTH
"Jonathan Orgel" <Jonathan@.srssoft.com> wrote in message
news:ek1TflmUGHA.1728@.TK2MSFTNGP11.phx.gbl...
> 1) We have a trigger which applies further changes to the inserted/updated
> records. Is this fundamentally bad or an acceptable practice?
> 2) We suspect that one version of such a trigger is causing deadlocks.
> Interestingly this does not seem to happen if we use a cursor. See two
> versions below. Any insights why the behavior differs?
> Looking forward to your comments,
> Jonathan Orgel
> -- Suspected of causing dead lock
> CREATE TRIGGER IU_DOCUMENTS ON DOCUMENTS
> FOR INSERT, UPDATE
> AS
> BEGIN
> UPDATE DOCUMENTS SET X=Y WHERE DOCUMENTID IN (SELECT DOCUMENTID FROM
> INSERTED)
> END
> -- OK...
> CREATE TRIGGER IU_DOCUMENTS ON DOCUMENTS
> FOR INSERT, UPDATE
> AS
> BEGIN
> DECLARE IndexCursor CURSOR LOCAL STATIC FOR SELECT DOCUMENTID FROM
> INSERTED
> OPEN IndexCursor
> FETCH NEXT FROM IndexCursor INTO @.DOCUMENTID
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> UPDATE DOCUMENTS SET X=Y WHERE DOCUMENTID = @.DOCUMENTID
> FETCH NEXT FROM IndexCursor INTO @.DOCUMENTID
> END
> CLOSE IndexCursor
> DEALLOCATE IndexCursor
> END
>
>

rewriting Oracle trigger to MSSQL

Hello,

I have this trigger :
CREATE OR REPLACE TRIGGER PLN_TEST
BEFORE INSERT
ON
TEST
REFERENCING
New AS MyNew
Old AS MyOld
FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
v_new_syscode TEST.SYSCODE%TYPE;
BEGIN
if :MyNew.Syscode is null then

PLN_GETNEWID('TEST',v_new_syscode);
commit;
:MyNew.SYSCODE :=v_new_syscode;
end if;
END
;
/

How can I rewrite it to MSSQL

ThanksProviding that your table has a unique record identifier field, the following will do the trick:

create trigger PLN_TEST on TEST for INSERT as
update t set syscode = newid()
from inserted i
inner join TEST t
on i.record_id = t.record_id
where t.syscode is null|||The problem is that i can get my id in oracle in a AUTONOMOUS_TRANSACTION, i think that this can not be done in MSSQL
Is this true.|||The entire UPDATE in proposed trigger is autonomous. Of course, it should be followed by this code to meet the basic programming standards:

if @.@.error != 0 begin
raiserror ('Update failed!', 15, 1)
rollback transaction
end

rewrite triggers as stored procedures

Could someone please help me rewrite these triggers as stored procedures.

Thanks

Trigger 1

ALTER TRIGGER trPaidInvoices

ON dbo.Invoices

FOR UPDATE

AS

IF UPDATE (InvoiceTotal)

insert into ClosedInvoices (InvoiceID,CustomerID,[Date])

SELECT InvoiceID, CustomerID, [Date]

FROM Invoices

WHERE (InvoiceTotal = 0.00)

Delete from invoices where invoiceTotal=0.00

select * from Invoices

Trigger 2

ALTER TRIGGER trInvoiceDetails_IU_UpdateMiscQty

ON dbo.InvoiceDetails

FOR INSERT, UPDATE

AS

/* IF UPDATE () ...*/

IF @.@.ROWCOUNT = 0

RETURN

IF UPDATE (Qty)

UPDATE MiscInventory

SET MiscInventory.InStock = ( MiscInventory.InStock - i.Qty )

FROM inserted i

JOIN MiscInventory

ON i.ItemID = MiscInventory.ItemID and i.ItemNumber=MiscInventory.ItemNumber

Trigger 3

ALTER TRIGGER trPayment_UD_InvoiceTotal

ON dbo.Payments

FOR INSERT, UPDATE

AS

IF UPDATE (Payment)

UPDATE Invoices

SET Invoices.InvoiceTotal= ( Invoices.InvoiceTotal - i.Payment )

FROM inserted i

JOIN Invoices

ON i.InvoiceID = Invoices.InvoiceID

Why do you want to do that?

You won't be able to access inserted/deleted in an SP but you can insert the contents into a temp table and access that.

Your trPaidInvoices is odd - it works on the whole table and does a select at the end!

Also does this work? If it's an update you should reverse the old value and add the new unless the update is always frmo 0.

Does if update work for an insert? I think it doesn't catch one of insert and delete not suer which.

Also what if someone updates to the same value?

|||

I was told it was "safer" to use sp instead of triggers.

The triggers work fine actually just want to know how to do the same with sp's

( the select on the trPaidInvoices is just my typo copied from the designer)

Cam

Reusing parameters in a subreport.

Is it possible to reuse parameters from a primary query to trigger the query in a subreport?

My primary report does totals sold grouped by salesman code, and now we need grand totals per salesmanin the report footer.

So what I did was create a subreport that gives me exactly what I need. Now for my problem. We use the visual basic ActiveX control for our users to query the database for the report.

It was fine before since all the user had to do was enter a from_date, to_date, and a state parameter and the report would do it's thing. Now with the subreport it asks for...

from_date
to_date
state
from_date (SalesManTotals)
to_date (SalesManTotals)
state (SalesManTotals)

So our users are forced to enter the same parameters twice. Once from the VB interface, and then a second time from a Crystal Reports box requesting the same parameters for the subreport.

How can I tell the report to use the first set of parameters for both reports without being requested to enter them a second time for the subreport?Try linking those parameter fields to the field in the subreport that contains that data

reusing a single conversation handle

Hi

I have a replicated table that has a trigger attached to the it. The trigger fires off a service broker message for inserts. Originally for every insert, I would begin a conversation, send, and end the conversation when target send an end conversation. Since replication process is only using a single spid, I would like to reuse 1 conversation. the following is what I have for the send procedure in the initiator. I check the conversation_endpoints for any open conversation, if it's null, I start a new conversation and send else just send with the existing conversation. Is there anything wrong with this code? What could cause the conversation on the initiator to be null if I never end the conversation on the initiator side? thanks

DECLARE @.dialog_handle uniqueidentifier

select @.dialog_handle = conversation_handle from sys.conversation_endpoints where state = 'CO'

IF @.dialog_handle is NULL

BEGIN DIALOG CONVERSATION @.dialog_handle

FROM SERVICE [initiator]

TO SERVICE 'target'

ON CONTRACT [portcontract];

SEND ON CONVERSATION @.dialog_handle

MESSAGE TYPE [Port] (@.msg)

Hi

Reusing conversations is generally a good idea and one good way to achieve this is to store a mapping of spid to conversations handle in a lookup table. http://blogs.msdn.com/remusrusanu/archive/2007/05/02/recycling-conversations.aspx goes into the details and helps answer your questions.

Thanks

Ketan