Differntiate Insert/Update in Trigger

I have a trigger like this

CREATE TRIGGER [TR_INSREC] ON [dbo].[SUB_MIS]
FOR INSERT, UPDATE
AS
IF <insert operation>
logic1
ELSE IF <update operation>
logic2
END IF
END

Now I want to know how can I get whether the trigger fires for insert or update operation. Because for insert, I put logic1 and for update, I put logic2...

Kindly help.....

You have to use Inserted & Deleted spl tables, these trigger scoped tables both table structure will be same as your main base table.

For Insert:

Only Inserted Table have data

Deleted Table will be empty

For Update:

Both Inserted & Deleted table have data

For Delete:

Inserted Table will be EMPTY

Only Deleted Table have data

Code Snippet

CREATE TRIGGER TR_INSREC ON dbo.SUB_MIS FOR INSERT, UPDATE, DELETE

AS

Begin

Declare @.InsertCount as Int;

Declare @.DeleteCount as Int;

Select @.InsertCount =0, @.DeleteCount=0

Select Top 1 @.InsertCount = 1 From Inserted;

Select Top 1 @.DeleteCount = 1 From Deleted;

IF @.InsertCount=1 And @.DeleteCount=0

Begin

--Your Insert logic1

Print 'Insert'

End

IF @.InsertCount=1 And @.DeleteCount=1

Begin

--Your Update Logic

Print 'Update'

End

IF @.InsertCount=0 And @.DeleteCount=1

Begin

--Your Delete Logic

Print 'Delete'

End

End

|||

Hi subhendude,

if you have different logic, why don;t you simply make 2 triggers - one for insert and one for delete?

Different result running SQL query directly or using SqlCommand

Hi, when running the following stored procedure:

ALTER PROCEDURE [dbo].[GetWerknemersBijLeidinggevende]
@.LeidinggevendeIDint,
@.Startint = 1,
@.Limitint = 25,
@.Sofinummerint =NULL,
@.Achternaamnvarchar(128) =NULL,
@.Functienvarchar(64) =NULLAS
WITH OrderedAS(SELECTROW_NUMBER()OVER (ORDER BY Achternaam)AS RowNumber,
Persoon.*
FROM Persoon
INNERJOIN Dienstverband
ON Persoon.ID = Dienstverband.PersoonID
INNERJOIN Bedrijfsonderdeel
ON Bedrijfsonderdeel.ID = Dienstverband.BedrijfsonderdeelID
INNERJOIN Leidinggevende
ON Bedrijfsonderdeel.ID = Leidinggevende.BedrijfsonderdeelID
WHERELeidinggevende.Begindatum <=getdate()
AND
(
Leidinggevende.Einddatum >getdate()
OR Leidinggevende.EinddatumISNULL)AND Leidinggevende.PersoonID = @.LeidinggevendeID
AND(Sofinummer = @.SofinummerOR @.SofinummerISNULL)AND(AchternaamLIKE @.Achternaam
OR AchternaamPartnerLIKE @.Achternaam
OR @.AchternaamISNULL))SELECT *
FROM Ordered
WHERE RowNumberbetween @.Startand (@.Start + @.Limit - 1)

When I run this in the database and fille de LeidinggevendeID parameter with a value I get a few rows returned, however when I run the following code:

[DataObject(true)]
public class PersoonFactory
 {
 [DataObjectMethod(DataObjectMethodType.Select,false)]
public static IList WerknemersBijLeidinggevende(int ldgID,int start,int max)
 {
 IList list =new List();
 SqlDataReader rdr =null;
 SqlConnection connection = DatabaseProvider.Connection;
 SqlCommand command =new SqlCommand("GetWerknemersBijLeidinggevende", connection);
 command.Parameters.AddWithValue("LeidinggevendeID", ldgID);
 command.CommandType = CommandType.StoredProcedure;
try { connection.Open(); rdr = command.ExecuteReader(CommandBehavior.CloseConnection);while (rdr.Read())
 {
 Persoon pers =new Persoon();
 pers.ID = rdr["ID"]as int?;
 pers.Achternaam = rdr["Achternaam"]as string;
 pers.AchternaamPartner = rdr["AchternaamPartner"]as string;
 pers.Achtertitels = rdr["Achtertitels"]as string;
 pers.DatumOverlijden = rdr["DatumOverlijden"]as DateTime?;
 pers.Geboortedatum = rdr["Geboortedatum"]as DateTime?;
 pers.Geslacht = rdr["Geslacht"]as string;
 pers.Middentitels = rdr["Middentitels"]as string;
 pers.Naamgebruik = (int)rdr["Naamgebruik"];
 pers.Sofinummer = rdr["Sofinummer"]as string;
 pers.Voorletters = rdr["Voorletters"]as string;
 pers.Voortitels = rdr["Voortitels"]as string;
 pers.Voorvoegsel = rdr["Voorvoegsel"]as string;
 pers.VoorvoegselPartner = rdr["VoorvoegselPartner"]as string;
 list.Add(pers);
 }
 }
catch {throw;
 }
finally {if (rdr !=null) rdr.Close();
else connection.Close();
 }
return list;
 }

I get 0 rows all of a sudden. Any idea why?

Your code works fine in my testing. You may need to capture a SQL Profiler trace to see what has been passed to SQL Server in the call of the SP, and what has been returned from SQL Server.

BTW, I notice in the SP you compare a column with a parameter in this way:

Sofinummer = @.Sofinummer
OR @.SofinummerISNULL

Since the @.Sofinummer can be null, so when both Sofinummer and @.Sofinummer are null, the expression "Sofinummer = @.Sofinummer" will return true if ANSI_NULLS option is off; and will return false when ANSI_NULLS if on. This won't cause confusion in this procedure since you add a "OR @.SofinummerISNULL" to deal with the null situation. However, we can use a ISNULL function to avoid such potential confusing result:

Sofinummer = ISNULL(@.Sofinummer,Sofinummer)

|||

I always thought that the original posters way was the better approach since one half of x=@.x OR @.x IS NULL can be quickly optimized out of the query (and not need to be evaluated on each row) since one half of it can be determined before the query is even executed and simplified, or isn't SQL Server that intelligent yet? That is unless SQL Server has been specifically told that x=ISNULL(@.x,x) is a special case, and also knows how to optimize that out of the query when @.x is/is not NULL.

For example

DECLARE @.x varchar(20)

SET @.x=NULL

SELECT *

FROM MyTable

WHERE (x=@.x OR @.x IS NULL)

without even executing the query, the where clause could be optimized to WHERE ((x=@.x) OR NULL IS NULL) -> WHERE ((x=@.x) OR TRUE) -> WHERE TRUE.

In the case @.x=1 it could be optimized to: WHERE ((x=@.x) OR 1 IS NULL) -> WHERE ((x=@.x) OR FALSE) -> WHEREx=@.x

|||

"Your code works fine in my testing. You may need to capture a SQL Profiler trace to see what has been passed to SQL Server in the call of the SP, and what has been returned from SQL Server."

Thanks for the reply. Well, since I am rather new to MS SQL (come from a MySQL background): How do I capture a SQL Profiler trace? Could you refer me to any documentation regarding this procedure?

Regards,

Dirk Louwers

|||

Motley:

I always thought that the original posters way was the better approach since one half of x=@.x OR @.x IS NULL can be quickly optimized out of the query (and not need to be evaluated on each row) since one half of it can be determined before the query is even executed and simplified, or isn't SQL Server that intelligent yet? That is unless SQL Server has been specifically told that x=ISNULL(@.x,x) is a special case, and also knows how to optimize that out of the query when @.x is/is not NULL.

Yes, I agree with you on this What I mean is that usingx=@.x where both x and @.x can be null may lead to nondeterministic result, based on the ANSI_NULLS setting.

Actually using x=ISNULL(@.x,x) will always add a nested loop in the execution plan so the performance may drop down when the table is huge. However when using "x=@.x OR @.x IS NULL", if @.x is null, SQL Server is not aware that actually the parameters are NULL and such clause won't filter any record, thus in the execution plan it may still uses index/table scan [+nested loop (if the query joins mutilple tables)] to retrieve the record.

To get max performance for such query, the best way it to execute different statements according to whether the parameters are null.If queries with such searching criterials are in a Stored Procedure, sometimes we can improve the performance by simply passing the values of the parameters to local variables defined in the SP, and then use the local variables in the queries. We have discussed such issue in a post In SQL2005, we have a new option new option for optimizing queries--Plan Guides. I have updated the post, please check it:

http://forums.asp.net/2/1377161/ShowThread.aspx

|||

Dirk_L:

Thanks for the reply. Well, since I am rather new to MS SQL (come from a MySQL background): How do I capture a SQL Profiler trace? Could you refer me to any documentation regarding this procedure?

Go to All Programms->Microsoft SQL Server2005->Performance Tools->SQL Server Profiler. You have to add Events and DataColumns according to what you want to trace. Since we just want to see what's happened when the stored procedure is executed, just start a trace using the TSQL_SPs template. To learn more about SQL Profiler, you can start from here:

http://msdn2.microsoft.com/en-us/library/ms187929.aspx

|||

Unfortunately I am using SQL Server 2005 Express on my development machine and the profiler doesn't seem to be included. Is there another options available other than moving the whole development project over to the production server and run it there?

|||If you have SQL2005 on your production server, you can use the Profiler on the production server to capture trace on your development server. If you don't have Profiler, you can print the SQL command just before you executing it to see whether it looks like you expected.

Different Date Formats when using xp_cmdshell

Hi all,
If I use the TSQL command:-
EXEC master.dbo.xp_cmdshell 'dir /a/od c:\*.*'
What determines the date format from of the output? I have 2 PC's (XP and
W2K) both with the same regional settings (DMY), both giving the same date
format output (DMY) for the dir command in a command window, but giving
different date formats with the above command.
I've tried changing the regional settings in the CP and also using the
DATEFORMAT command but the nothing changes the date format of the output.
I want to make sure the command returns the same format regardless of the
database, country or anyother SQL option/setting.
Thanks in advance.
Greg
greghines@.bigfoot.com.NOSPAM
Remove NOSPAM when replyingWho is logged into the machine in each case? What account is SQL Server
running under? What windows user is issuing the command?
The only way you can expect the output to be consistent is to be sure that
regional settings on all machines are identical, as are the regional
settings of all system accounts (e.g. the one SQL Server runs under) and all
accounts that may be logged in at the time the command is run.
A
"Greg Hines" <greghines@.bigfoot.com.NOSPAM> wrote in message
news:_PP9f.162$_j6.6553@.nnrp1.ozemail.com.au...
> Hi all,
> If I use the TSQL command:-
> EXEC master.dbo.xp_cmdshell 'dir /a/od c:\*.*'
> What determines the date format from of the output? I have 2 PC's (XP and
> W2K) both with the same regional settings (DMY), both giving the same date
> format output (DMY) for the dir command in a command window, but giving
> different date formats with the above command.
> I've tried changing the regional settings in the CP and also using the
> DATEFORMAT command but the nothing changes the date format of the output.
> I want to make sure the command returns the same format regardless of the
> database, country or anyother SQL option/setting.
> Thanks in advance.
> Greg
>
> --
> greghines@.bigfoot.com.NOSPAM
> Remove NOSPAM when replying
>|||The only logged in user in both cases is sa.
Account for both is dbo
It's not a windows user it's sa. Both use only SQL Authentication.
As I said the regional settings are the same. Proven by the fact that the
dir command in a command window gives the same date format on both PCs.
Greg
--
greghines@.bigfoot.com.NOSPAM
Remove NOSPAM when replying
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:e%23Zf2Ay3FHA.696@.TK2MSFTNGP09.phx.gbl...
> Who is logged into the machine in each case? What account is SQL Server
> running under? What windows user is issuing the command?
> The only way you can expect the output to be consistent is to be sure that
> regional settings on all machines are identical, as are the regional
> settings of all system accounts (e.g. the one SQL Server runs under) and
all
> accounts that may be logged in at the time the command is run.
> A
>
> "Greg Hines" <greghines@.bigfoot.com.NOSPAM> wrote in message
> news:_PP9f.162$_j6.6553@.nnrp1.ozemail.com.au...
and
date
output.
the
>|||> The only logged in user in both cases is sa.
You can't be logged into Windows as sa. Believe it or not, this user may
have a bearing on how dir returns results.
Also, SQL Server is either running as a specific Windows user, or the "local
system" account. The service itself does not run as dbo or sa -- Windows
has absolutely no idea what those mean.
A

Differencies in triggers behavior on 2000 and 2005 SQL Servers

This code works fine on MS SQL 2000 but fails on MS SQL 2005:

create database tt

go

--exec dbo.sp_dbcmptlevel @.dbname=N'tt', @.new_cmptlevel=80

go

use tt
go

create table t (id int)
go

create proc p as update t set id=id
go

create trigger t1_i on t instead of update as update t set id=id
go

create trigger t1 on t for update as if @.@.nestlevel > 5 return exec p update t set id=id
go

insert t select 1
go

update t set id=id
go

use master
go
drop database tt
go

Using database compatibility level 80 (SQL 2000, uncomment the line starting with --exec dbo.sp_dbcmptlevel...) does not make this code work like it does on SQL 2000!

We have examined this code and found that changing

create trigger t1 on t for update as if @.@.nestlevel > 5 return
exec p -- this procedure just executes "update t set id=id" and fails

to direct "update"

create trigger t1 on t for update as if @.@.nestlevel > 5 return
update t set id=id -- works fine without any errors

makes this code work fine but our procedure p is too complex to do any workarounds...

With which error does the procedure fail ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Msg 570, Level 16, State 1, Procedure p, Line 2

Instead of triggers do not support direct recursion. Trigger execution failed.

|||Hi,

do you have another triggers defined on the table ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

There are no other triggers. That code (in the first message) is enough complete to reproduce the error.

Sestrin

|||I believe this has been submitted to MS Connect, http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=248294 and awaiting workaround.