Difference between [Stored Procedure / Trigger & Function]

hi

can any give me what are the major difference between [Stored Procedure / Trigger & Function] with example

kinds regards

There's no need for examples. The distinctions are very bold.

A stored procedure generally performs query's on one or more tables. i.e. UPDATE, INSERT, DELETE

A trigger is tied to one table and fires based on the type of trigger you use.

A function generally doesn't touch any table but simple performs a task such as complex calculations or string munipulation and returns a value.

Notice I use the term generally and I mean it very loosely. All of these can overlap but the choice of use is based on the desired task.

Adamus

sql

Determining Trigger Order

I'm not sure if this is the correct forum or not for this question. Basically, i have 6 triggers attached to my table (2 of each type). I used sp_settriggerorder to make sure certian triggers fire first.

Is there any way to go back and determine what order a trigger has been set to (first, none or last)? I've scanned through all of the system tables and don't see anything.

Thanks in advance.

With some digging, you can find that they encode the order into the status fields on sysobjects. However, the way they are exposed is with the ObjectProperty() function -- it takes parameters such as ExecIsFirstUpdateTrigger.

It is not obvious to me how triggers are exposed from the Information_Schema views.

The digging involves using sp_helptext on the sp_settriggerorder and sp_helptrigger stored procedures.

|||That's exactly what I was looking for.

I didn't even think abou tusing sp_helptext to see what the procedure did. I'll definitely use that next time I want to know what SQL is doing.

Thanks!

Determining trigger execution state

Is there a way to determine if a trigger fired due to an insert or update
operation. I need to know this for the following reason. I have fields in
my tables that I want to initialize only when a record is inserted. If the
record is later updated, I do not want these fields changed. I also do not
want to have to maintain two seperate triggers to do this. In SqlAnywhere,
they have variables you check in the trigger to determine this as the
following example illustrates.
IF Inserting
!do inserting code
END IF
IF Updating
!do updating code
END IF
Is there something similar to this in SQL server?
Thanks in advance for any help.
Mark GenovyHi Mark
If any rows were affected, an insert trigger will only populate the inserted
table, but an update trigger will populate both inserted and deleted. So I
usually just look at the count(*) value from these tables. However, if no
rows were affected, the triggers will still fire, and both inserted and
deleted will have 0 rows. So then you can't tell what operation occurred.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Mark Genovy" <Mark Genovy@.discussions.microsoft.com> wrote in message
news:24381F86-0662-4913-A703-139BC0A9DF46@.microsoft.com...
> Is there a way to determine if a trigger fired due to an insert or update
> operation. I need to know this for the following reason. I have fields
> in
> my tables that I want to initialize only when a record is inserted. If
> the
> record is later updated, I do not want these fields changed. I also do
> not
> want to have to maintain two seperate triggers to do this. In
> SqlAnywhere,
> they have variables you check in the trigger to determine this as the
> following example illustrates.
> IF Inserting
> !do inserting code
> END IF
> IF Updating
> !do updating code
> END IF
> Is there something similar to this in SQL server?
> Thanks in advance for any help.
> Mark Genovy