Help with Trigger

Question

Help with Trigger

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Stix83 SSC Eights! Points: 956 More actions · Answer 1

what i am trying now is to break it up - to first see if i can generate the QT number in my custom table: I have started my trigger as below and if i insert an opportunity the client_id is inserted but the QT (quote number isnt) probably because i havent set it to be inserted..

here is the trigger as per my thread above:

create trigger oninsert on Amgr_opportunity_tbl
for insert
as
begin
Declare @client_id varchar (20);
Declare @contact_number int;
Declare @QT int;
Select @client_id = client_id;
select @contact_number = i.contact_number from inserted i

insert into QT (QT, client_id)
values (@QT,@client_id)
end
GO

Where can i set the quote number insert in that specific format as the above only gives me the below result:

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Stix83 - Monday, October 16, 2017 6:38 AM
what i am trying now is to break it up - to first see if i can generate the QT number in my custom table: I have started my trigger as below and if i insert an opportunity the client_id is inserted but the QT (quote number isnt) probably because i havent set it to be inserted..
here is the trigger as per my thread above:
create trigger oninsert on Amgr_opportunity_tbl
for insert
as
begin
Declare @client_id varchar (20);
Declare @contact_number int;
Declare @QT int;
Select @client_id = client_id;
select @contact_number = i.contact_number from inserted i
insert into QT (QT, client_id)
values (@QT,@client_id)
end
GO
Where can i set the quote number insert in that specific format as the above only gives me the below result:

Where's the QT table come from?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Stix83 SSC Eights! Points: 956 More actions · Answer 3

its a custom table that i created:

CREATE TABLE [dbo].[QT](

[QT] [int] NULL,

[client_id] [varchar](20) NULL

) ON [PRIMARY]

GO

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Is there ever likely to be more than onerow at a time added to the opportunity table? This is important because it willdetermine how the trigger is written. Consider for instance the possibility ofa bunch of rows coming in as an import from another system or another part ofthe same system. Note that the sample trigger you posted earlier is asingle-row trigger, if you attempted to insert a bunch of rows in a singlebatch, it would either fail, or generate incorrect results.

I’m concerned that you don’t seem to knowor care how the key columns of the opportunity table are generated. If there’san existing mechanism for generating new numbers or strings, isn’t that whatyou should be using?

These questions aside, if you want asingle-row insert trigger then it should be straightforward, something likethis:

CREATE TRIGGER [dbo].[MyTrigger] ON [dbo].[MyTable] AFTER INSERT AS BEGIN

SET NOCOUNT ON;

DECLARE @client_id VARCHAR(20)
SELECT @client_id = client_id FROM inserted

DECLARE @NextID INT, @NewQT VARCHAR(20)
UPDATE dbo.QT WITH (UPDLOCK) SET @NextID = QT = QT + 1
WHERE [client_id] = @client_id
SET @NewQT = 'Q' + CAST(@NextID AS VARCHAR(4)) + '/' + RIGHT(CONVERT(VARCHAR(10),GETDATE(),103),7)

INSERT INTO [dbo].[AMGR_User_Fields_Tbl] (
  [Type_ID],
  NewQT,
  client_id)
SELECT
  [Type_ID] = 127,
  @NewQT,
  i.client_id
FROM inserted i

END

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Stix83 SSC Eights! Points: 956 More actions · Answer 5

ChrisM@Work - Monday, October 16, 2017 7:48 AM
Is there ever likely to be more than onerow at a time added to the opportunity table? This is important because it willdetermine how the trigger is written. Consider for instance the possibility ofa bunch of rows coming in as an import from another system or another part ofthe same system. Note that the sample trigger you posted earlier is asingle-row trigger, if you attempted to insert a bunch of rows in a singlebatch, it would either fail, or generate incorrect results.
Iâ€™m concerned that you donâ€™t seem to knowor care how the key columns of the opportunity table are generated. If thereâ€™san existing mechanism for generating new numbers or strings, isnâ€™t that whatyou should be using?
These questions aside, if you want asingle-row insert trigger then it should be straightforward, something likethis:
CREATE TRIGGER [dbo].[MyTrigger] ON [dbo].[MyTable] AFTER INSERT AS BEGIN
SET NOCOUNT ON;
DECLARE @client_id VARCHAR(20)
SELECT @client_id = client_id FROM inserted
DECLARE @NextID INT, @NewQT VARCHAR(20)
UPDATE dbo.QT WITH (UPDLOCK) SET @NextID = QT = QT + 1
WHERE [client_id] = @client_id
SET @NewQT = 'Q' + CAST(@NextID AS VARCHAR(4)) + '/' + RIGHT(CONVERT(VARCHAR(10),GETDATE(),103),7)
INSERT INTO [dbo].[AMGR_User_Fields_Tbl] (
  [Type_ID],
  NewQT,
  client_id)
SELECT
  [Type_ID] = 127,
  @NewQT,
  i.client_id
FROM inserted i
END

The current system is a CRM based system, whenever an entry is added to the system - a client_ID is generated for this entry.
The user can insert more than one opportunity in the day however in the user fields table - the type_id is then associated with the client_id to distinguish which record to assign the quote number to.