Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Introduction to DML Triggers


Introduction to DML Triggers

Author
Message
Robert-378556
Robert-378556
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1568 Visits: 1007
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.
Jack Corbett
  Jack Corbett
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11014 Visits: 14858
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45022 Visits: 39887
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? Smile

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jack Corbett
  Jack Corbett
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11014 Visits: 14858
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.Smile



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
Sue-651097
Sue-651097
Valued Member
Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)

Group: General Forum Members
Points: 59 Visits: 87
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 Smile, i would be glad about any suggestions.

Susanne
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45022 Visits: 39887
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.Smile


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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
timothyawiseman
timothyawiseman
SSC Eights!
SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)

Group: General Forum Members
Points: 800 Visits: 920
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/
Jack Corbett
  Jack Corbett
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11014 Visits: 14858
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
Robert-378556
Robert-378556
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1568 Visits: 1007
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.

Smile
timothyawiseman
timothyawiseman
SSC Eights!
SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)

Group: General Forum Members
Points: 800 Visits: 920
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/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search