September 1, 2014 at 2:40 am
Hey guys, is it possible to delete from a table,then insert into that same table, and then delete from a second table, all in one INSERT trigger?
September 1, 2014 at 2:45 am
I believe so, but there's only one way to find out for sure. Do you have a test server?
John
September 1, 2014 at 2:56 am
crazy_new (9/1/2014)
Hey guys, is it possible to delete from a table,then insert into that same table, and then delete from a second table, all in one INSERT trigger?
Recursive trigger is possible. You need to set database level setting: Recursive triggers enabled = true.
Any specific reason to do the same - delete the record & insert same record again?
Thanks
September 1, 2014 at 2:58 am
I do, it only goes up to step one, it never continues to step two.
DELETE FROM NAME_ADD_FIELDS
WHERE NAME_ID = @NameID
AND NAME_FIELD_NUMBER = @RoleID
INSERT INTO NAME_ADD_FIELDS
SELECT
@NameID,
@RoleID,
@Signature,
NULL,
NULL
DELETE FROM PAT_ACCOUNT_MANAGER
WHERE PAT_ACCOUNT_MANAGER_ID > (
SELECT
MAX(PAT_ACCOUNT_MANAGER_ID) AS PAT_ACCOUNT_MANAGER_ID
FROM
PAT_ACCOUNT_MANAGER
WHERE
ROLE_TYPE_ID = @RoleID
AND
NAME_ID = @NameID
GROUP BY
ROLE_TYPE_ID
)
It does the first delete, but then it doesn't go on to the insert or the second delete
September 1, 2014 at 2:59 am
crazy_new (9/1/2014)
Hey guys, is it possible to delete from a table,then insert into that same table, and then delete from a second table, all in one INSERT trigger?
Yup. If it's not working post the complete trigger code.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 1, 2014 at 3:01 am
Please will you post the CREATE TABLE and CREATE TRIGGER statements for the table and trigger?
Thanks
John
September 1, 2014 at 3:39 am
USE [Patricia]
GO
/****** Object: Trigger [dbo].[INSERT_TK_NameIDExtendedTAB] Script Date: 08/29/2014 01:53:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[INSERT_TK_NameIDExtendedTAB]
ON
[dbo].[PAT_ACCOUNT_MANAGER]
FOR
INSERT
AS
DECLARE @PAMID int, @RoleID int,@NameID int, @Signature varchar(3),@MaxID int
SET @PAMID = (
SELECT
MAX (PAT_ACCOUNT_MANAGER_ID)
FROM
PAT_ACCOUNT_MANAGER
)
SET @NameID = (
SELECT
NAME_ID
FROM
PAT_ACCOUNT_MANAGER
WHERE
PAT_ACCOUNT_MANAGER_ID = @PAMID
)
SET @RoleID = (SELECT
CASE ROLE_TYPE_ID
WHEN 20035 THEN 20009
WHEN 20036 THEN 20008
END
FROM
PAT_ACCOUNT_MANAGER
WHERE
PAT_ACCOUNT_MANAGER_ID = @PAMID
)
SET @Signature = (SELECT
[Signature]
FROM
PERSON P
JOIN
PAT_ACCOUNT_MANAGER PAM ON P.LOGIN_ID = PAM.LOGIN_ID
WHERE
PAT_ACCOUNT_MANAGER_ID = @PAMID
)
SET @MaxID = (
SELECT
MAX(PAT_ACCOUNT_MANAGER_ID)
FROM
PAT_ACCOUNT_MANAGER
WHERE NAME_ID = @NameID
)
BEGIN
DELETE FROM NAME_ADD_FIELDS
WHERE NAME_ID = @NameID
AND NAME_FIELD_NUMBER = @RoleID
END
BEGIN
INSERT INTO NAME_ADD_FIELDS
SELECT
@NameID,
@RoleID,
@Signature,
NULL,
NULL
END
BEGIN
DELETE FROM PAT_ACCOUNT_MANAGER
WHERE exists (
SELECT
MAX(PAT_ACCOUNT_MANAGER_ID) AS PAT_ACCOUNT_MANAGER_ID
FROM
PAT_ACCOUNT_MANAGER
GROUP BY
ROLE_TYPE_ID
)
and ROLE_TYPE_ID = @RoleID
and NAME_ID = @NameID
END[/sub][/sub]
[/sup]
[highlight=#ffff11]Now that I have put the begin end statements there it moves on BUT it only executes if the second part of the case statement is true, it doesn't insert when it is the first part.[/highlight]
September 1, 2014 at 3:43 am
There's a lot wrong with that trigger. To start, the fact that it does not reference the inserted table, hence isn't working with the rows just inserted. Also looks like it's designed for one row, not batches.
Several steps back (ie rewrite), what's it supposed to do?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 1, 2014 at 3:57 am
First table that I am deleting from, i'm inserting the new values into. The reason I have to delete from that table is to avoid duplicate values, because this table does allow duplicates and has no way to determine a unique row. So I want to remove the old value and insert the new value. In the front end you can't update the values, only insert and delete. (Name_Add_Fields)
The second table I am deleting from is for the exact same reason, but there is a Unique value in this table. And the only time I want to remove the duplicate value is obviously when I put a new value in.(Pat_Account_Manager).
The way it determines which one to remove is by the @RoleID on the @NameID. @NameID is the client and @RoleID is the Role.
September 1, 2014 at 6:02 am
CREATE TABLE [dbo].[PAT_ACCOUNT_MANAGER](
[PAT_ACCOUNT_MANAGER_ID] [int] NOT NULL,
[NAME_ID] [int] NULL,
[LOGIN_ID] [char](20) NULL,
[ROLE_TYPE_ID] [int] NULL,
CONSTRAINT [pk_pat_account_manager] PRIMARY KEY CLUSTERED
(
[PAT_ACCOUNT_MANAGER_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
September 1, 2014 at 6:05 am
When I take the last delete away, the trigger works perfectly. It adds everything it has to. I would just like it to delete the records from the last table to because it would be a lot better if the user doesn't have to ad and then delete, instead they can use the insert button as an update one too.
September 1, 2014 at 7:34 am
crazy_new (9/1/2014)
The way it determines which one to remove is by the @RoleID on the @NameID. @NameID is the client and @RoleID is the Role.
But you're not using the inserted table, so you have no idea what's been inserted.
Read up on the inserted and deleted pseudo-tables, then have a look at rewriting the trigger based on the rows in the inserted table (the newly inserted rows)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 1, 2014 at 7:57 am
Yes, your code appears to assume that the row that was inserted is the one with the maximum PAT_ACCOUNT_MANAGER_ID. Even if that column had an identity property, that would be a dangerous assumption to make, but since it doesn't, your trigger is even more likely to fail. Worse than that, it could do something unexpected but not return an error at all. I assume you're relying on your application to generate the next number in the sequence?
Gail is right - you need to use the Inserted virtual table to get the value(s) you're looking for. The "(s)" is important, because sometimes more than one row might be inserted in a single transaction.
John
September 1, 2014 at 10:00 am
crazy_new (9/1/2014)
Quick questions below
😎
First table that I am deleting from, i'm inserting the new values into. The reason I have to delete from that table is to avoid duplicate values, because this table does allow duplicates and has no way to determine a unique row.
How do you determine what the old value is?
So I want to remove the old value and insert the new value. In the front end you can't update the values, only insert and delete. (Name_Add_Fields)
The second table I am deleting from is for the exact same reason, but there is a Unique value in this table. And the only time I want to remove the duplicate value is obviously when I put a new value in.(Pat_Account_Manager).
Is that unique value passed as parameter or do you do a lookup by any specific values in the original insert?
The way it determines which one to remove is by the @RoleID on the @NameID. @NameID is the client and @RoleID is the Role.
September 1, 2014 at 10:38 am
Here is somewhat a generic sample, should get you passed this hurdle. This is not a complete solution but rather a demonstration of the technique.
😎
First the schema and the sample data
USE tempdb;
GO
/* TEST DDL AND SAMPLE DATA*/
CREATE TABLE dbo.THE_INSERT_TABLE
(
TIT_ID INT NOT NULL
,TIT_STR1 VARCHAR(10) NOT NULL
,TIT_STR2 VARCHAR(10) NOT NULL
);
CREATE TABLE dbo.FIRST_UPDATE_TABLE
(
TIT_ID INT NOT NULL CONSTRAINT PK_DBO_FIRST_UPDATE_TABLE_TIT_ID
PRIMARY KEY CLUSTERED
,TIT_STR1 VARCHAR(10) NOT NULL
,TIT_STR2 VARCHAR(10) NOT NULL
);
CREATE TABLE dbo.SECOND_DELETE_TABLE
(
TIT_ID INT NOT NULL CONSTRAINT PK_DBO_SECOND_DELETE_TABLE_TIT_ID
PRIMARY KEY CLUSTERED
,TIT_STR1 VARCHAR(10) NOT NULL
,TIT_STR2 VARCHAR(10) NOT NULL
);
INSERT INTO dbo.THE_INSERT_TABLE
(
TIT_ID
,TIT_STR1
,TIT_STR2
)
VALUES
(10,'A10','B10')
,(11,'A11','B11')
,(12,'A12','B12')
,(13,'A13','B13')
,(14,'A14','B14')
,(15,'A15','B15');
INSERT INTO dbo.FIRST_UPDATE_TABLE
(
TIT_ID
,TIT_STR1
,TIT_STR2
)
VALUES
(90,'A10','B10')
,(91,'A11','B11')
,(92,'A12','B12')
,(93,'A13','B13')
,(94,'A14','B14')
,(95,'A15','B15');
INSERT INTO dbo.SECOND_DELETE_TABLE
(
TIT_ID
,TIT_STR1
,TIT_STR2
)
VALUES
(20,'A10','B10')
,(31,'A11','B11')
,(42,'A12','B12')
,(53,'A13','B13')
,(64,'A14','B14')
,(75,'A15','B15');
And then the trigger code
CREATE TRIGGER dbo.TRG_DELETE_DUPE_ON_INSERT
ON dbo.THE_INSERT_TABLE
AFTER INSERT
AS
BEGIN
DELETE T
FROM dbo.THE_INSERT_TABLE T
INNER JOIN inserted I
ON T.TIT_STR1 = I.TIT_STR1
AND T.TIT_STR2 = I.TIT_STR2
INSERT INTO dbo.THE_INSERT_TABLE
(TIT_ID,TIT_STR1,TIT_STR2)
SELECT TIT_ID,TIT_STR1,TIT_STR2
FROM inserted
UPDATE F
SET F.TIT_ID = I.TIT_ID
FROM inserted I
INNER JOIN dbo.FIRST_UPDATE_TABLE F
ON I.TIT_STR1 = F.TIT_STR1
AND I.TIT_STR2 = F.TIT_STR1
DELETE X
FROM inserted I
INNER JOIN dbo.SECOND_DELETE_TABLE X
ON I.TIT_ID = X.TIT_ID
INSERT INTO dbo.SECOND_DELETE_TABLE
(TIT_ID,TIT_STR1,TIT_STR2)
SELECT TIT_ID,TIT_STR1,TIT_STR2
FROM inserted
END
Finally a small test
SELECT * FROM dbo.THE_INSERT_TABLE;
SELECT * FROM dbo.FIRST_UPDATE_TABLE;
SELECT * FROM dbo.SECOND_DELETE_TABLE;
INSERT INTO dbo.THE_INSERT_TABLE (TIT_ID,TIT_STR1,TIT_STR2) VALUES (101,'A11','B11');
SELECT * FROM dbo.THE_INSERT_TABLE;
SELECT * FROM dbo.FIRST_UPDATE_TABLE;
SELECT * FROM dbo.SECOND_DELETE_TABLE;
"After" Results
TIT_ID TIT_STR1 TIT_STR2
----------- ---------- ----------
10 A10 B10
11 A11 B11
12 A12 B12
13 A13 B13
14 A14 B14
15 A15 B15
TIT_ID TIT_STR1 TIT_STR2
----------- ---------- ----------
90 A10 B10
91 A11 B11
92 A12 B12
93 A13 B13
94 A14 B14
95 A15 B15
TIT_ID TIT_STR1 TIT_STR2
----------- ---------- ----------
20 A10 B10
31 A11 B11
42 A12 B12
53 A13 B13
64 A14 B14
75 A15 B15
101 A11 B11
Viewing 15 posts - 1 through 15 (of 43 total)
You must be logged in to reply to this topic. Login to reply