SQL Trigger to check if value has been inserted already after insert

Transact-SQL
,
1

I have a trigger that works on one instance (windows platform GUI) but not in the Web platform (GUI)
My trigger is below:

create trigger [dbo].[Trig] on [dbo].[AMGR_Opportunity_Tbl] after insert
as

BEGIN
Declare @Opp_id varchar (24)
Declare @OppNo int
Declare @Opp_type int
DECLARE @NewQno VARCHAR(750)
declare @Qno varchar (50)

select @Opp_id = client_id from INSERTED;
Select @OppNo = Contact_number from INSERTED;
Select @Opp_type = Opp_type from inserted;

SELECT @NewQno = Convert(VARCHAR(750), Convert(INT, Qno) + 1)
FROM T1 WHERE ID = 1;

If @Opp_type = '0'

SELECT @NewQno = Convert(VARCHAR(750), Convert(INT, Qno) + 1)
FROM T1 WHERE ID = 1;

if exists (select * from O_QUDF where Client_Id = @Opp_id)
update O_QUDF set O_QUDF = @NewQno
else
INSERT INTO O_QUDF(Client_id,Contact_Number,Type_Id,Code_Id,O_QUDF)
values(@Opp_id,@OppNo,110,0,@NewQno)

UPDATE T1 SET Qno = @NewQno WHERE ID = 1

End

GO

I need to add in a check to see if the values being inserted are there or not. The error i am getting on the web interface is a foreign key contraint duplication.

the procedure of this trigger should work as follows:

  1. Trigger fired on Opportunity table when Opp type = 0 only
  2. Calculation to pick up last number used (in table T1)
  3. Add 1 to last number used in T1
  4. Insert the last number used into the column O_QUDF in the O_QUDF table
  5. update Table T1 to record last number used.

As far as i can see i think i am following the logic above however it isnt working as it should.
Each time an opportunity is inserted into the Opportunity table with opp_type = 0, then the trigger should fire and do the above logic..

As i said, it works in the windows application GUI not on the web. i just cant seem to get this code right after getting numerous examples on various forums.. If someone can point me in the right direction as to what i can try. i would greatly appreciate it.

2

Probably this:

update O_QUDF set O_QUDF = @NewQno

which will update EVERY row in that table.

Triggers need to work for multiple rows in the recordset. This will only work for one row, at random, in the recordset.

I strongly recommend that you modify your trigger to handle multiple rows, because sooner or later there will be an update / insert to the table involving multiple rows.

At the very least add a RAISERROR / ROLLBACK if there is more than one rows in [inserted]

You ought to wrap your Exists+UPDATE/INSERT and subsequent UPDATE in a transaction, otherwise you run the risk that you get one-but-not-the-other if something goes wrong.

3

for my trigger it will only work for one row inserted at a time.. this is how the program front end works.
as per the above - i need assistance of what the code should look like or how i should change it

4

I cannot completely follow your logic but something like the following should help.

-- Assuming that only meant to process where inserted.Opp_type = 0
SET ANSI_NULLS, QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER dbo.TRI_AMGR_Opportunity_Tbl
ON dbo.AMGR_Opportunity_Tbl
AFTER INSERT
AS
BEGIN
	-- XACT_ABORT to rollback if errors.
	-- Would otherwise need TRY, CATCH etc.
	SET NOCOUNT, XACT_ABORT ON;
	DECLARE @rc int;

	-- Always allow for processing multiple rows.
	SELECT I.client_id, I.contact_number
		,CAST(ROW_NUMBER() OVER (ORDER BY I.client_id) + CAST(T1.Qno AS int) AS varchar(750)) AS NewQno
	INTO #Rows2Process
	FROM inserted I
		-- This is horrible for concurrency!
		-- UPLOCK needed to stop other processes getting the same value
		CROSS JOIN T1 WITH (UPDLOCK)
	WHERE T1.ID = 1
		AND I.Opp_type = '0';

	SET @rc = @@ROWCOUNT;

	IF (@rc > 0)
	BEGIN
		UPDATE U
		SET O_QUDF = P.NewQno
		FROM O_QUDF U
			JOIN #Rows2Process P
				ON U.client_Id = P.client_id;

		INSERT INTO O_QUDF(Client_id,Contact_Number,[Type_Id],Code_Id,O_QUDF)
		SELECT client_id, contact_number, 110, 0, NewQno
		FROM #Rows2Process P
		WHERE NOT EXISTS
		(
			SELECT 1
			FROM O_QUDF U
			WHERE U.client_Id = P.client_id
		);

		UPDATE T1
		SET Qno =  CAST(CAST(Qno AS int) + @rc AS varchar(750))
		WHERE ID = 1;
	END
END
GO
5

And what if someone imports some data direct into the database table?

You need to build triggers to handle multiple records - or put in some code that raises an error if someone tries to do that.

Was it not the

update O_QUDF set O_QUDF = @NewQno

that was causing the Foreign Key error?

6

You need to use set-based code in SQL triggers. Hopefully something like this, if I properly understood the original trigger code:

CREATE TRIGGER [dbo].[Trig_Insert] 
ON [dbo].[AMGR_Opportunity_Tbl] 
AFTER INSERT
AS
Set Nocount On;
If Not Exists(Select 1 From inserted)
    RETURN;

Declare @Qno varchar(50)

UPDATE T1
SET @Qno = Qno = Qno + (SELECT COUNT(*) FROM inserted)
WHERE ID = 1

UPDATE O_Q
SET O_QUDF = T1.Qno_base + i.row_num
SELECT i.client_id, i.Contact_number, i.Opp_type,
    Row_Number() OVER(ORDER BY i.client_id, i.contact_number) AS row_num
FROM inserted i
Cross Apply (
    SELECT @Qno - (SELECT COUNT(*) FROM inserted) AS Qno_base
) AS T1
INNER JOIN dbo.O_QUDF O_Q ON O_Q.Client_Id = i.client_id

INSERT INTO O_QUDF(Client_id,Contact_Number,Type_Id,Code_Id,O_QUDF)
SELECT i.client_id, i.Contact_number, 110, 0,
    Qno_base + Row_Number() OVER(ORDER BY i.client_id, i.contact_number) AS Qno_New
FROM inserted i
Cross Apply (
    SELECT @Qno - (SELECT COUNT(*) FROM inserted) AS Qno_base
) AS T1
LEFT OUTER JOIN dbo.O_QUDF O_Q ON O_Q.Client_Id = i.client_id
WHERE O_Q.Client_Id IS NULL
GO
7

Is a TRANSACTION needed in a Trigger (with multiple Insert/Update statements)? or is it OK to rely on the implied-transaction?

8

Hi All

I rewrote my trigger as per the below: It works with Standard numbers like 1, 2, 3 etc:

CREATE TRIGGER [dbo].[Set_QUDF_For_New_Opps] ON [dbo].[AMGR_Opportunity_Tbl]
AFTER INSERT
AS
DECLARE @RecordId int
DECLARE @NewQno varchar(750)

BEGIN
SET ROWCOUNT 0
SET NOCOUNT ON

IF EXISTS( SELECT * FROM inserted ) BEGIN

DECLARE I CURSOR LOCAL FAST_FORWARD FOR
SELECT Record_Id FROM Inserted;
OPEN I
FETCH NEXT FROM I INTO @RecordId;
WHILE @@FETCH_STATUS = 0 BEGIN

SELECT @NewQno = Convert(VARCHAR(255), Convert(INT, Qno) + 1)
FROM T1 WHERE ID = 1;

INSERT INTO O_QUDF(Client_id,Contact_Number,O_QUDF)
SELECT Opp_Id, 0, @NewQno
FROM inserted WHERE Record_Id = @RecordId AND Opp_Type = 0;

UPDATE T1 SET Qno = @NewQno WHERE ID = 1;

FETCH NEXT FROM I INTO @RecordId;
END
CLOSE I
DEALLOCATE I
END
END

GO

Now the numbering has to be as follows: Q1500/11/2017 (Where 11 is month and 2017 the year, which rolls over each month and year) the 1500 just keeps incrementing by 1 each time the trigger fires.

I have i have tried the following:

select @NewQno = 'Q' + '/' + Convert(VARCHAR(255), Convert(INT, Quote_No) + 1) + '/' + RIGHT(convert(varchar(10), getdate(),103), 7) from Quote_Pref_No where ID = 1

But it throws an insert error when the trigger fires in the GUI

This is my new custom table:

CREATE TABLE [dbo].[Quote_Pref_No](
[ID] [int] IDENTITY(1,1) NOT NULL,

[Quote_Pref] varchar NULL,

[Quote_No] [int] NULL,
[Yr] varchar NULL,
[Mth] varchar NULL
) ON [PRIMARY]

So i just need to get the format right for the numbers then im good to go.

9

You've added requirements and you're determined to use a cursor. I can't help you further down that path. Hopefully someone else can.