Showing posts with label trigger. Show all posts
Showing posts with label trigger. Show all posts

Monday, March 26, 2012

Problem with Intead of Insert Trigger

I'm having a rather weird problem with an Instead Of Insert trigger.
Its supposed to check to see if the record exists in the destination table,
and if so update it, otherwise insert the row in the table. All pretty
straightforward, but I'm getting weird behavior.
If the destination table is empty, each row gets properly inserted. If there
are records in the destination table, any that are found get properly
updated, but no new records get inserted.
Here is my table layout:
CREATE TABLE [dbo].[TestProspect] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[ProspectNumber] [int] NULL ,
[iFirstName] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[iLastname] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[iPhone] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[pFirstName] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[pLastName] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[pPhone] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Type] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
And here is my trigger:
CREATE TRIGGER [tr_IMPRMSProspect] ON [dbo].[IMP_Prospect]
INSTEAD OF INSERT
AS
--try to update the inserted record to an existing record
UPDATE p
SET
pLastName = ISNULL(i.LastName,''),
pFirstName = ISNULL(i.FirstName,''),
pPhone = ISNULL(i.Phone1,''),
Type = 'UPDATE'
FROM TestProspect p INNER JOIN INSERTED i ON
p.iPhone = i.Phone1 AND
p.iLastName = i.LastName AND
p.iFirstName = i.FirstName
WHERE p.ProspectNumber < 4000000 or p.ProspectNumber >= 4999999
--if it isn't in the table, insert it
INSERT INTO TestProspect
SELECT i.ProspectNumber, i.FirstName, i.LastName, i.Phone1, '', '', '',
'INSERT'
FROM INSERTED i
WHERE NOT EXISTS (
SELECT p.ProspectNumber
FROM TestProspect p INNER JOIN INSERTED i ON
p.iPhone = i.Phone1 AND
p.iLastName = i.LastName AND
p.iFirstName = i.FirstName)
I've tried all kinds of things, like wrapping it in an if-else clause or
rearranging the insert/update order, to no avail. Anybody see something that
I'm missing?
Thanks in advance.Verify that ANSI_NULLS was set ON when the trigger was created. Also, if
{iFirstName, iLastName, iPhone} is a key, then they shouldn't be nullable,
and you should create a unique constraint to ensure that you don't get any
duplicates.
"Brian Levine" <Brian Levine@.discussions.microsoft.com> wrote in message
news:462FFE90-CF5E-4575-B357-2B7D64E3B382@.microsoft.com...
> I'm having a rather weird problem with an Instead Of Insert trigger.
> Its supposed to check to see if the record exists in the destination
> table,
> and if so update it, otherwise insert the row in the table. All pretty
> straightforward, but I'm getting weird behavior.
> If the destination table is empty, each row gets properly inserted. If
> there
> are records in the destination table, any that are found get properly
> updated, but no new records get inserted.
> Here is my table layout:
> CREATE TABLE [dbo].[TestProspect] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [ProspectNumber] [int] NULL ,
> [iFirstName] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [iLastname] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [iPhone] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [pFirstName] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [pLastName] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [pPhone] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Type] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> And here is my trigger:
> CREATE TRIGGER [tr_IMPRMSProspect] ON [dbo].[IMP_Prospect]
> INSTEAD OF INSERT
> AS
> --try to update the inserted record to an existing record
> UPDATE p
> SET
> pLastName = ISNULL(i.LastName,''),
> pFirstName = ISNULL(i.FirstName,''),
> pPhone = ISNULL(i.Phone1,''),
> Type = 'UPDATE'
> FROM TestProspect p INNER JOIN INSERTED i ON
> p.iPhone = i.Phone1 AND
> p.iLastName = i.LastName AND
> p.iFirstName = i.FirstName
> WHERE p.ProspectNumber < 4000000 or p.ProspectNumber >= 4999999
> --if it isn't in the table, insert it
> INSERT INTO TestProspect
> SELECT i.ProspectNumber, i.FirstName, i.LastName, i.Phone1, '', '', '',
> 'INSERT'
> FROM INSERTED i
> WHERE NOT EXISTS (
> SELECT p.ProspectNumber
> FROM TestProspect p INNER JOIN INSERTED i ON
> p.iPhone = i.Phone1 AND
> p.iLastName = i.LastName AND
> p.iFirstName = i.FirstName)
> I've tried all kinds of things, like wrapping it in an if-else clause or
> rearranging the insert/update order, to no avail. Anybody see something
> that
> I'm missing?
> Thanks in advance.|||Try this for the insert portion:
INSERT INTO TestProspect
SELECT i.ProspectNumber, i.FirstName, i.LastName, i.Phone1, '', '', '',
'INSERT'
FROM INSERTED i
WHERE NOT EXISTS (
SELECT 1
FROM TestProspect p WHERE
p.iPhone = i.Phone1 AND
p.iLastName = i.LastName AND
p.iFirstName = i.FirstName)
"Brian Levine" wrote:

> I'm having a rather weird problem with an Instead Of Insert trigger.
> Its supposed to check to see if the record exists in the destination table
,
> and if so update it, otherwise insert the row in the table. All pretty
> straightforward, but I'm getting weird behavior.
> If the destination table is empty, each row gets properly inserted. If the
re
> are records in the destination table, any that are found get properly
> updated, but no new records get inserted.
> Here is my table layout:
> CREATE TABLE [dbo].[TestProspect] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [ProspectNumber] [int] NULL ,
> [iFirstName] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [iLastname] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [iPhone] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [pFirstName] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [pLastName] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [pPhone] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Type] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> And here is my trigger:
> CREATE TRIGGER [tr_IMPRMSProspect] ON [dbo].[IMP_Prospect]
> INSTEAD OF INSERT
> AS
> --try to update the inserted record to an existing record
> UPDATE p
> SET
> pLastName = ISNULL(i.LastName,''),
> pFirstName = ISNULL(i.FirstName,''),
> pPhone = ISNULL(i.Phone1,''),
> Type = 'UPDATE'
> FROM TestProspect p INNER JOIN INSERTED i ON
> p.iPhone = i.Phone1 AND
> p.iLastName = i.LastName AND
> p.iFirstName = i.FirstName
> WHERE p.ProspectNumber < 4000000 or p.ProspectNumber >= 4999999
> --if it isn't in the table, insert it
> INSERT INTO TestProspect
> SELECT i.ProspectNumber, i.FirstName, i.LastName, i.Phone1, '', '', '',
> 'INSERT'
> FROM INSERTED i
> WHERE NOT EXISTS (
> SELECT p.ProspectNumber
> FROM TestProspect p INNER JOIN INSERTED i ON
> p.iPhone = i.Phone1 AND
> p.iLastName = i.LastName AND
> p.iFirstName = i.FirstName)
> I've tried all kinds of things, like wrapping it in an if-else clause or
> rearranging the insert/update order, to no avail. Anybody see something th
at
> I'm missing?
> Thanks in advance.

Problem with INSTEAD OF UPDATE trigger

Maybe this is a silly question, but I am not used to SQL Server.

I have a view where I put an INSTEAD OF UPDATE trigger. In this trigger, I have to deal with the two trigger tables: inserted and deleted. My problem is that I don't know what to do when an UPDATE command changes more than one row in my view. In this case, both inserted and deleted tables have more than one row. How can I know which inserted record correspond to a given deleted record?join them on the primary key.|||Originally posted by nigelrivett
join them on the primary key.

But what should I do when the primary key is changing? In this case the PKs in 'inserted' and 'updated' will not match.|||...yet another reason to use artificial primary keys...|||I am used to Oracle triggers. In Oracle, you can create a FOR EACH ROW trigger, which enables you to match the :new and :old records. Is is very easy, and doesn't require an artificial PK.

I don't want to add a new column to my view just to deal with this limitation of SQL Server. Is there another alternative? Maybe there is some function that helps us to match the records, but I still could not find one...|||Originally posted by blindman
...yet another reason to use artificial primary keys...

Not another "surrogate" for the use of IDENTITY...

Oh the humanity...

Why do you need know? What's the code doing...why can;t this be done the procedure that makes the update? (because you allow dynamic sql, right)

If you post the trigger, maybe we can come up with some ideas...|||That may be easier, but it is essentially a cursor and could not be as efficient as a set-based operation against a true primary key.

You could try to match on another set of columns that constitues a natural key.

...but you should avoid updates to views anyway. It is better programming practice (for many reasons) to handle input and output from tables through stored procedures. Most of the views I've seen created were made by developers who had stepped up from MS Access and its saved querys. Most experienced dbas I've met strongly perfer to use stored procedures.

blindman|||>> But what should I do when the primary key is changing? In this case the PKs in 'inserted' and 'updated' will not match.

A PK is a record identifier. If you update it (possibly an indication of poor choice of PK) then this is a logical delete/insert so failing to match rows is correct.
If you carry out the physical delete and insert for this then you won't have this problem otherwise anything unmatched as a deleted record and and inserted record in the trigger.|||My problem is that I am dealing with legacy code, that uses a table that does not exist anymore. The old table was replaced by my view. I can't (and don't want to) change the legacy code. It uses (yes) a lot of dynamic SQL, that makes changes also in the PK. I wanted to make the view work transparently, just like the old table. The view has a lot of joins, so I cannot just create an updateable view. When the user changes a record in the view, he is really changing records in two or three distinct tables.

My application uses both Oracle and SQL Server databases. I could solve the problem easily in Oracle, but don't know how to do it in SQL Server.|||Unfortunately sql server has less features that contravene rdbms rules so is more suseptable to problems due to bad design.

Don't think there is much you can do about this other than correct the implementation.

You could traverse the inserted and deleted tables row by row (or insert the recs into temp tables with identities) and hope that the record order obtained corresponds to the matching records but there is no guarantee that this will always work even if it happens to on some instances.|||I understand your use of the temporary table to avoid having to rewrite a ton of code. That makes sense in many instances.

You will still need to add a unique column to your table, which other procedures do not even need to be aware of, and which you can use for matching up records. This is your best option.

blindman|||Yes. I created a new column in my view, a copy of the PK that is not changed by the legacy code. This will be my new unique ID in the trigger.

Quick and dirty, but it solves the problem. Thanks a lot for the suggestion.sql

Problem with Insert trigger

I have an insert trigger that works 99.999% of the time. The trigger is
fired via a dts process that runs every 10 minutes which inserts data into a
ForecastTonnageChanges table. Based on changes to this
ForecastTonnageChanges, I want to update the live Forecast table. The
trigger fires this event. My basic logic in the trigger is:
Delete from Forecast
where exists (select *
from inserted
where <joining key columns> )
Insert into Forecast
select ...
from inserted
When this doesn't work, I am ending up with extra records in my forecast
table. I'm wondering if another insert has happened and between the delete
and the insert in the trigger, an additional row is in the inserted table.
Is this possible? I would think implied locking via the trigger would
prevent this. But, I'm grasping for straws here.
If you have ideas on a more reliable way to implement this... please share.Hi
A trigger is fired for each statement, therefore you will not get extra rows
in the inserted/deleted tables.
As you don't give DDL and all the trigger code it is hard to say where you
are going wrong, but you may want to use profiler to see what is happening.
You should also implement error handling to make sure that the statement
succeeds and all the relivent statements in the transaction are rolled back
if a failure occurs.
John
"Erin" wrote:
> I have an insert trigger that works 99.999% of the time. The trigger is
> fired via a dts process that runs every 10 minutes which inserts data into
a
> ForecastTonnageChanges table. Based on changes to this
> ForecastTonnageChanges, I want to update the live Forecast table. The
> trigger fires this event. My basic logic in the trigger is:
> Delete from Forecast
> where exists (select *
> from inserted
> where <joining key columns> )
> Insert into Forecast
> select ...
> from inserted
> When this doesn't work, I am ending up with extra records in my forecast
> table. I'm wondering if another insert has happened and between the delet
e
> and the insert in the trigger, an additional row is in the inserted table.
> Is this possible? I would think implied locking via the trigger would
> prevent this. But, I'm grasping for straws here.
> If you have ideas on a more reliable way to implement this... please share.[/color
]|||Without any more information, could you have a forecast VIEW of the
most current rows in the ForecastTonnageChanges table instead of
physically shuffling all this data around? You obviously have a time
stamp on the new data, so that should be easy enough.

Problem with INSERT Trigger

It's not so much a problem, as I don't know how to get around this
issue. We have applications that rely on the return value of a stored
procedure. This stored procedure inserts a record into a table that has
a trigger. The trigger calls a couple of stored procedures itself.

Due to the trigger, we have a return value of 0 coming in ahead of the
return value for the stored procedure we call directly. I've tested
this in Query Analyzer as well, and the same behavior applies.

In case I didn't describe it clearly:

Call storedproc1

storedproc1 inserts a record into table1

table1 has an INSERT trigger

expected behavior is: storedproc1 returns value1 and value2

actual behavior is: storedproc1 returns 0, then returns value 1 and
value 2(timothy.alvis@.gmail.com) writes:

Quote:

Originally Posted by

It's not so much a problem, as I don't know how to get around this
issue. We have applications that rely on the return value of a stored
procedure. This stored procedure inserts a record into a table that has
a trigger. The trigger calls a couple of stored procedures itself.
>
Due to the trigger, we have a return value of 0 coming in ahead of the
return value for the stored procedure we call directly. I've tested
this in Query Analyzer as well, and the same behavior applies.
>
In case I didn't describe it clearly:
>
Call storedproc1
>
storedproc1 inserts a record into table1
>
table1 has an INSERT trigger
>
expected behavior is: storedproc1 returns value1 and value2
>
actual behavior is: storedproc1 returns 0, then returns value 1 and
value 2


Returns? This needs some clarification. A stored procedure can return
data in three different ways:
o Result set
o Output parameters
o Return value. as in EXEC @.ret = some_sp

Which do you mean?

Overall, it would help if you posted the code of the procedure, so we know
what you are talking about. Please also include the output when run the
procedure in Query Analyzer.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Saturday, February 25, 2012

Problem with delete trigger

I am trying to write a delete trigger that fires when the user deletes the only record for a given entity in the table. In this situation I have an entity PMA which can have one or many uses. I want the trigger to fire if the user tries to delete the last use record for that PMA_NUM from the PMA_USE table.

Here is my code:

if (SELECT Count(pma_use.pma_num) FROM dbo.PMA_USE
inner join dbo.deleted as D1 on dbo.PMA_USE.PMA_NUM= d1.PMA_NUM) = 0
BEGIN
RAISERROR ('Cannot delete only Use for PMA!', 16, 1)
ROLLBACK TRANSACTION
END

I seem to be getting inconsistent results when I create the trigger and when I try to test it re the deleted table

Sometimes when I run the trigger script in Management Studio Express, it has trouble with my using dbo.deleted and sometimes it doesn't

When I try to test the trigger by using MSE to view the table and delete the last use record for a PMA, I get an invalid object cannot find 'dbo.deleted' or cannot find 'deleted'

What am I doing wrong?

Roberta

1 - Use just [deleted]

2 - you do not need to count in order to prove existence. Use EXISTS operator.

if exists(select * from dbo.PMA_USE as a innner join deleted as b on a.PMA_NUM = b.PMA_NUM)

BEGIN
RAISERROR ('Cannot delete only Use for PMA!', 16, 1)
ROLLBACK TRANSACTION
END

AMB

Monday, February 20, 2012

Problem with DATEDIFF

I currently have a problem with DATEDIFF(). When I run the below code, it works the way I expect it should. However, running this logic in a trigger produces a different result. I can consistenly reproduce this and have not figured out the cause.

DECLARE

@.Hours INT,

@.LastPlayDate DATETIME,

@.CardOutTime DATETIME

set @.LastPlayDate ='1900-01-01 00:00:00.000'

set @.CardOutTime ='2005-04-22 18:33:51.000'

SET @.hOURS =DATEDIFF(hh, @.LastPlayDate, @.CardOutTime)

ifDATEDIFF(hh, @.LastPlayDate, @.CardOutTime)> 12

BEGIN

print'greater'

SELECT @.HOURS

END

else

BEGIN

print'lessor'

SELECT @.HOURS

END

Trigger Code

IFDATEDIFF(hh, @.LastPlayDate, @.CardOutTime)> 12--@.TripLength

-- @.Hours > @.TripLength

SET @.NewTrip = 1;

ELSE

SET @.NewTrip = 0;

I know that the last play date is less than the card out time and that the hours between these is greater than 12 in this csae and this shows correctly in the results pane. When this logic is implemented in a trigger, instead of the results being "greater", I get "lesser". When I set the results to another variable so I can see what the value is, it always returns a negative number. Any insight into this is appreaciated.

Hi MAGrimsley,

I can not reproduce the problem. I am using SS2005 DE SP2.

createtable dbo.t1 (

c1 int

)

go

createtrigger tr_t1_ins on dbo.t1

forinsert

as

DECLARE @.Hours INT, @.LastPlayDate DATETIME, @.CardOutTime DATETIME

declare @.NewTrip int

set @.LastPlayDate ='1900-01-01 00:00:00.000'

set @.CardOutTime ='2005-04-22 18:33:51.000'

IFDATEDIFF(hh, @.LastPlayDate, @.CardOutTime)> 12

SET @.NewTrip = 1;

ELSE

SET @.NewTrip = 0;

select @.NewTrip

go

insertinto dbo.t1 defaultvalues

go

droptable dbo.t1

go

Result:

1

AMB

|||

AMB

That is the mystifying part. I copy the code into another window and it runs the way I expect it to. The fact it is in a trigger should not make a difference, but the result I get is. I have even rewritten the trigger, but the result is the same.

MAGrimsley

|||Version--SQL Server 2005 Enterprise (IA64) 9.00.3054.00