Instead of Insert trigger query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Instead of Insert trigger query

Author

Topic

speedy_gonzalos
Starting Member

10 Posts

Posted - 2009-05-11 : 08:38:51

Hi All,

This is my first time ever in participating in a forum so please be patient if i may any mistakes!

I have created a trigger on the SQL server which is invoked when an insert is attempted on the DealerPayments table. A web form will be used to input the data into this table. The purpose of my trigger is to check if a CustomerNo exists, if it deosnt then it allows the new entry to be entered into the table as a new record. But if the CustomerNo exists then to only update the other values apart from CustomerNo and not to insert it as a new record. The trigger is as follows:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[CheckCustNo]
ON [dbo].[DealerPayments]
INSTEAD OF INSERT AS

DECLARE @rowcnt int

BEGIN
SELECT @rowcnt = COUNT(*) FROM DealerPayments d, Inserted I WHERE d.CutomerNo = I.CutomerNo

IF @rowcnt = 0
INSERT INTO DealerPayments (DealerID,CutomerNo,ChequeNo,Amount,IssueDate,InputDate,Notes,InvoiceReceived)
SELECT DealerID,CutomerNo,ChequeNo,Amount,IssueDate,InputDate,Notes,InvoiceReceived
FROM Inserted
ELSE
UPDATE DealerPayments
SET DealerID = I.DealerID, ChequeNo = I.ChequeNo, Amount = I.Amount, IssueDate = I.IssueDate, InputDate = I.InputDate, Notes = I.Notes, InvoiceReceived = I.InvoiceReceived
FROM DealerPayments d, Inserted I
WHERE d.CutomerNo = I.CutomerNo
END

The above trigger works fine when inserting a record at a time, it does the required insert or update action, however the problem that I am having is when i am doing a bulk insert:

INSERT INTO DealerPayments (DealerID,CutomerNo,ChequeNo,Amount,IssueDate,Notes)
SELECT 1,5,1,5.00,'04/15/2009','Test6'
UNION ALL
SELECT 1,53681,1,35.00,'04/16/2009','999'

It updates the row that already exists in the table (i.e. CustomerNo=53681) but it will not add the new record (i.e. CustomerNo = 5). it is setting the rowcnt = 1 for the statement and then only executing the Update statement in the trigger.

My question is how can i get it to execute the trigger for each row that is inserted?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-11 : 08:50:21

try . .


BEGIN
-- SELECT @rowcnt = COUNT(*) FROM DealerPayments d, Inserted I WHERE d.CutomerNo = I.CutomerNo

UPDATE 	DealerPayments
SET 	DealerID = I.DealerID, ChequeNo = I.ChequeNo, Amount = I.Amount, IssueDate = I.IssueDate, InputDate = I.InputDate, Notes = I.Notes, InvoiceReceived = I.InvoiceReceived
FROM 	DealerPayments d 
	INNER JOIN Inserted I	on	d.CutomerNo = I.CutomerNo

-- IF @rowcnt = 0
INSERT INTO DealerPayments (DealerID,CutomerNo,ChequeNo,Amount,IssueDate,InputDate,Notes,InvoiceReceived)
SELECT 	DealerID,CutomerNo,ChequeNo,Amount,IssueDate,InputDate,Notes,InvoiceReceived
FROM 	Inserted
WHERE	NOT EXISTS
	(
		SELECT	*
		FROM	DealerPayments x
		WHERE	x.DealerID	= i.DealerID
		AND	x.CutomerNo	= i.CutomerNo
	)
-- ELSE
-- UPDATE DealerPayments
-- SET DealerID = I.DealerID, ChequeNo = I.ChequeNo, Amount = I.Amount, IssueDate = I.IssueDate, InputDate = I.InputDate, Notes = I.Notes, InvoiceReceived = I.InvoiceReceived
-- FROM DealerPayments d, Inserted I
-- WHERE d.CutomerNo = I.CutomerNo

END

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-11 : 10:34:12

why do it inside trigger? cant you do this check inside your insert procedure itself?

speedy_gonzalos
Starting Member

10 Posts

Posted - 2009-05-11 : 12:15:56

Mainly the data will be inserted using a web form, so that is why the trigger needs to be invoked to check that duplicate records of the same CustomerNo are not being inserted in the DealerPayments table.
The reason why I was testing bulk inserts, is that in some instances a need may arise to insert bulk data then the trigger needs to be following the same protocol for single record inserts.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-11 : 12:39:23

BULK INSERT wont invoke triggers unless you've FIRE TRIGGERS option set

speedy_gonzalos
Starting Member

10 Posts

Posted - 2009-05-12 : 04:41:21

I have read some stuff on FIRE TRIGGERS but i am not too sure of how i go about implementing it in the above code, if anyone can show me an example that would be great!

But...the trigger is being invoked for the BULK INSERT! That is why the update statement (in the trigger) is being actioned cos its setting the rowcnt = 1. It is reading the BULK INSERT as a batch but i need it to read each insert individually.

speedy_gonzalos
Starting Member

10 Posts

Posted - 2009-05-12 : 04:50:16

I have just tried Khtan suggestion but it still does do the bulk insert correctly. Any help would be most appreciated.

speedy_gonzalos
Starting Member

10 Posts

Posted - 2009-05-12 : 05:28:00

No worries, i have got it working now.
I just had to change the trigger to the following:

BEGIN

-- when CutomerNo exists then do an update
UPDATE DealerPayments
SET DealerID = I.DealerID,
ChequeNo = I.ChequeNo,
Amount = I.Amount,
IssueDate = I.IssueDate,
InputDate = I.InputDate,
Notes = I.Notes,
InvoiceReceived = I.InvoiceReceived
FROM Inserted I
INNER JOIN DealerPayments d
ON d.CutomerNo = I.CutomerNo
WHERE d.CutomerNo = I.CutomerNo

-- when CutomerNo does not exist then do an insert
INSERT INTO DealerPayments
(DealerID,CutomerNo,ChequeNo,Amount,IssueDate,InputDate,Notes,InvoiceReceived)
SELECT I.DealerID, I.CutomerNo, I.ChequeNo, I.Amount, I.IssueDate, I.InputDate, I.Notes, I.InvoiceReceived
FROM Inserted I
LEFT JOIN DealerPayments d
ON d.CutomerNo = I.CutomerNo
WHERE d.PaymentID IS NULL
END

Sometimes theres no need to over complicate matters. Honestly i thought i was going crazy but the answer could not have been simpler!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-12 : 06:44:00

[code]
BEGIN
-- when CutomerNo exists then do an update
UPDATE DealerPayments
SET DealerID = I.DealerID,
ChequeNo = I.ChequeNo,
Amount = I.Amount,
IssueDate = I.IssueDate,
InputDate = I.InputDate,
Notes = I.Notes,
InvoiceReceived = I.InvoiceReceived
FROM Inserted I
INNER JOIN DealerPayments d
ON d.CutomerNo = I.CutomerNo
~~WHERE d.CutomerNo = I.CutomerNo~~ -- redundant

-- when CutomerNo does not exist then do an insert
INSERT INTO DealerPayments
(DealerID,CutomerNo,ChequeNo,Amount,IssueDate,InputDate,Notes,InvoiceReceived)
SELECT I.DealerID, I.CutomerNo, I.ChequeNo, I.Amount, I.IssueDate, I.InputDate, I.Notes, I.InvoiceReceived
FROM Inserted I
LEFT JOIN DealerPayments d
ON d.CutomerNo = I.CutomerNo
WHERE d.PaymentID IS NULL
END
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources