Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234»»

Introduction to DML Triggers Expand / Collapse
Author
Message
Posted Wednesday, October 15, 2008 1:47 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, October 16, 2014 1:08 AM
Points: 1,248, Visits: 910
Jack, to my dismay, you're absolutely right. Raiserror in this form is just a message and execution continues normally.
I uncommented Select 'test' and added return 1 after raiserror and both are returned with error message.
Well, never too late to learn new things.
Post #586012
Posted Wednesday, October 15, 2008 6:43 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, October 17, 2014 1:18 PM
Points: 10,255, Visits: 13,218
Robert,

I'm gald we got this sorted out. I didn't go in trying to prove anything only to understand how it works.




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #586135
Posted Monday, November 3, 2008 6:53 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:45 PM
Points: 35,342, Visits: 31,875
Dang it, Jack! Somehow I missed this article when it was published. Found it as a link in one of your good posts. Awesome article especially for folks new to T-SQL.

Any chance of talking you into writing one about "Instead Of" triggers? :)


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #596248
Posted Tuesday, November 4, 2008 5:40 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, October 17, 2014 1:18 PM
Points: 10,255, Visits: 13,218
Thanks Jeff. I could write something more detailed about INSTEAD OF triggers, but I've never actually used one in one of my databases. I use stored procedures to do anything I'd do in an INSTEAD OF trigger. That's why the INSTEAD OF section was so short.:)



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #597006
Posted Tuesday, November 11, 2008 4:41 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, September 22, 2013 4:06 AM
Points: 54, Visits: 64
hi Jack,
thanks for your articel.
but I still have a question (hopefully it is ok to post questions/problems here, if not: sorry folks, i'm quite new here):
I get a csv string from the UI that has to be devides in several columns.
this procedure has to run very fast, so i splitted the necessary actions (because otherwise it is to slow and some information is lost):

1. procedure which inserts the whole csv string in one column. (this proc has to check if one csv string contains only one or more rows and then inserts it into the table.)
like '1211224,45345,35636,3453453,3453456,...)
2. job, which copies the new data as it is to another sql-server every 5 minutes (necessary because UI is located on a DMZ and the information has to be stored in the internal sql server)

now the trigger problem:
if the new data is inserted, i run a trigger, which splits the information in the different columns. I do not see any possibility to make an update for all the rows in the inserted as you mentioned, because the split works with a function which returns the value before the next coma of the csv-string.

create trigger trtest
...
declare @Hoehe int, @kmStand int, @text varchar(200)

select @text = text from inserted

-- @text is the csv string, @hoehe, @kmstand .. are variables which are used in the update statement
set @text = dbo.fKomma(@text)
set @Hoehe = dbo.fWert(@text)
set @text = dbo.fKomma(@text)
set @kmStand = dbo.fWert(@text)
....
update table set
hoehe = @hoehe,
kmstand = @kmstand,....
where id = @id
go

as the trigger is fired only once, i implemented a procedure
which contains a cursor to make the insert and therefore the trigger is fired for each row.

create proc pTransfer
as
set nocount on
declare tcursor cursor for
select daten
from tblOrtungExt
order by idortung

declare @daten varchar(500)

open tcursor

fetch next from tcursor
into @daten

while @@FETCH_STATUS = 0
begin
-- insert in tabel with trigger
exec pInsertOrtung @daten

fetch next from tcursor
into @daten
end

close tcursor
deallocate tcursor
go



very long problem...sorry for that. so if you have any time left :), i would be glad about any suggestions.


Susanne
Post #600560
Posted Tuesday, November 11, 2008 5:07 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:45 PM
Points: 35,342, Visits: 31,875
Jack Corbett (11/4/2008)
Thanks Jeff. I could write something more detailed about INSTEAD OF triggers, but I've never actually used one in one of my databases. I use stored procedures to do anything I'd do in an INSTEAD OF trigger. That's why the INSTEAD OF section was so short.:)


I think that everyone has the same thoughts there. I don't know anyone who has actually written an INSTEAD OF trigger for anything practical and wouldn't mind seeing one that's actually been tested and, hopefully, documented.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #600572
Posted Thursday, November 13, 2008 7:46 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, April 4, 2014 4:40 PM
Points: 751, Visits: 917
The one thing I can think of off hand, (and I have never done this in practice) where you would need an instead of trigger is for being able to do updates against views where the view would not normally be updatable.

But except for some truly unusual situations that I have yet to come across in practice, you would be better off either forcing everyone to use a stored procedure to update that information or designing your tables so that you have no need to update the view.

Still, it is an option.


---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
Post #602121
Posted Thursday, November 13, 2008 8:23 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, October 17, 2014 1:18 PM
Points: 10,255, Visits: 13,218
Yeah, INSTEAD OF triggers are an option, but so aren't cursors. I know cursors do have their use, so I am sure INSTEAD OF triggers do as well. I just have not found one yet.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #602168
Posted Thursday, November 13, 2008 11:28 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, October 16, 2014 1:08 AM
Points: 1,248, Visits: 910
For ideas see Normalizing-Denormalized Tables http://www.sqlservercentral.com/articles/Normalization/64428/ and the discussion.
My suggestion:
You can expand on this. What you got is backward compatibility for select queries. Additionally, you can create "instead of" triggers on views, so you get update compatibility too. Idea for part II of the article.

:)
Post #602291
Posted Thursday, November 13, 2008 12:51 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, April 4, 2014 4:40 PM
Points: 751, Visits: 917
Heh. Robert and Mohit have a point. Instead of triggers views could be used to let a legacy application interact with and change the data in the way it is designed to, while storing it in a completely separate fashion.

Still, those circumstances should be few and far between indeed. It is much better to change the application as well, and it is better still (in most cases) to put all updates and inserts through stored procedures and then the stored procedures can be changed to fit the data layer and the application will never be the wiser.


---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
Post #602333
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse