September 2, 2014 at 4:18 am
Here is my finished code. I have more than one trigger do get the job done, but it is working now. To determine the appropriate value, is just by using code. Say 1000 will be the value of an item in the front end to delete, so everything with this value will have the same name. Everything is stored in the in this table is stored in the same column. The only way it it differentiates in the front end is by code. Exapmle Column 1 will store all the codes and column 2 all the string data.
(INSERT INTO TABLE)
NameID RoleID StringData
3000 20008 LS
3000 20009 LS
2001 20007 Audi
(INSERT FROM TABLE)
ID NameID RoleID Login
1 3000 20010 LukeS
2 3000 20011 LukeS
Where Table 1 RoleID = Table 2 RoleID.(All (T1)20008 = (T2)20010 and (T1)20009 = (T2)20011) Only for those codes. Its a bit confusing if youre not used to it
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
inserted
)
SET @RoleID = (SELECT
CASE ROLE_TYPE_ID
WHEN 20035 THEN 20009
WHEN 20036 THEN 20008
END
FROM
inserted
)
SET @Signature = (SELECT
[signature]
FROM
PERSON p
join inserted i ON p.login_id = i.LOGIN_ID
)
--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
September 2, 2014 at 4:18 am
Here is my finished code. I have more than one trigger do get the job done, but it is working now. To determine the appropriate value, is just by using code. Say 1000 will be the value of an item in the front end to delete, so everything with this value will have the same name. Everything is stored in the in this table is stored in the same column. The only way it it differentiates in the front end is by code. Exapmle Column 1 will store all the codes and column 2 all the string data.
(INSERT INTO TABLE)
NameID RoleID StringData
3000 20008 LS
3000 20009 LS
2001 20007 Audi
(INSERT FROM TABLE)
ID NameID RoleID Login
1 3000 20010 LukeS
2 3000 20011 LukeS
Where Table 1 RoleID = Table 2 RoleID.(All (T1)20008 = (T2)20010 and (T1)20009 = (T2)20011) Only for those codes. Its a bit confusing if youre not used to it
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
inserted
)
SET @RoleID = (SELECT
CASE ROLE_TYPE_ID
WHEN 20035 THEN 20009
WHEN 20036 THEN 20008
END
FROM
inserted
)
SET @Signature = (SELECT
[signature]
FROM
PERSON p
join inserted i ON p.login_id = i.LOGIN_ID
)
--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
September 2, 2014 at 5:01 am
Two things with that. You're setting the value of @PAMID, but not using it downstream. That means you're doing unnecessary reads against PAT_ACCOUNT_MANAGER. Second, your trigger will fail if you insert more than one row at a time, since your SET statements will attempt to get multiple values into a scalar variable.
John
September 2, 2014 at 5:48 am
crazy_new (9/2/2014)
SET @PAMID = (SELECT
MAX (PAT_ACCOUNT_MANAGER_ID)
FROM
PAT_ACCOUNT_MANAGER
)
Still assuming that the highest value in the table is the one you're dealing with. Risky.
SET @NameID = (
SELECT
NAME_ID
FROM
inserted
)
What happens when multiple rows are inserted in a single operation?
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 2, 2014 at 6:08 am
Im not using that @PAMID variable anymore..just forgot to take it out before I posted the code. This trigger will fire for every insert. Is it possible for two or more records to be inserted exactly at the same time(by multiple users adding data) from the front end, if the application only allows you to insert one?
September 2, 2014 at 6:29 am
crazy_new (9/2/2014)
Im not using that @PAMID variable anymore..just forgot to take it out before I posted the code. This trigger will fire for every insert. Is it possible for two or more records to be inserted exactly at the same time(by multiple users adding data) from the front end, if the application only allows you to insert one?
Yes. In a trigger, always assume multiple rows and use the rows given to you as inserted/deleted/updated.make no assumptions about what should be in your table when the trigger runs.
Gerald Britton, Pluralsight courses
September 2, 2014 at 6:39 am
crazy_new (9/2/2014)
Is it possible for two or more records to be inserted exactly at the same time(by multiple users adding data) from the front end, if the application only allows you to insert one?
No, the trigger fires for each insert, so if two users insert a row each, the trigger fires twice, once for each.
That said, while the app inserts one row at a time now, what's preventing that from changing in the future? Maybe next week someone has to do an ad-hoc insert of 5 rows, maybe next month the app gets changed to insert batches.
It's generally not a good idea to leave time-bombs in your code, and that trigger currently is a time-bomb. If, at sometime in the future, for whatever reason, two or more rows are inserted in a single operation, the trigger will throw an error and fail.
It's easy to write triggers to properly handle any number of rows in the inserted table, easier than doing critical fixes to a prod system when the trigger breaks at some point.
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 2, 2014 at 6:50 am
In this specific situation your will never be able to add more than one, because this trigger is just for two people on a single client, one is a supervisor and one is the so called owner, and you can only have one of each per client. But by writing it to deal with batch inserts, would you write a cursor in to execute this for all the new ID's inserted?
September 2, 2014 at 6:55 am
crazy_new (9/2/2014)
But by writing it to deal with batch inserts, would you write a cursor in to execute this for all the new ID's inserted?
No.
You'd write is using insert and delete statements, based off the inserted table to affect all applicable rows in a single insert/delete operation.
I understand that currently the app won't allow multiple inserts at the moment. What happens when someone has to do some data imports? What happens if the app is changed?
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 2, 2014 at 6:55 am
crazy_new (9/2/2014)
In this specific situation your will never be able to add more than one, because this trigger is just for two people on a single client, one is a supervisor and one is the so called owner, and you can only have one of each per client. But by writing it to deal with batch inserts, would you write a cursor in to execute this for all the new ID's inserted?
Don't use a cursor (ever!) Use sets based logic
Gerald Britton, Pluralsight courses
September 2, 2014 at 7:08 am
Just a quick question. In the inserted/deleted tables, does it contain all of the records of the last single transaction made? And if I use set based logic, how would I put the name ID in a variable then?
September 2, 2014 at 7:10 am
crazy_new (9/2/2014)
Just a quick question. In the inserted/deleted tables, does it contain all of the records of the last single transaction made? And if I use set based logic, how would I put the name ID in a variable then?
Why use a variable?
Gerald Britton, Pluralsight courses
September 2, 2014 at 7:20 am
Sorry:blush: So then I assume that the inserted table does how all the records of you insert a batch?
September 2, 2014 at 7:32 am
crazy_new (9/2/2014)
Sorry:blush: So then I assume that the inserted table does how all the records of you insert a batch?
Yup!
Gerald Britton, Pluralsight courses
September 2, 2014 at 7:41 am
crazy_new (9/2/2014)
In the inserted/deleted tables, does it contain all of the records of the last single transaction made?
It contains all of the rows which the statement that fired the trigger affected. It only contains rows for the table the trigger is on (not all rows across all tables that the last transaction touched)
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
Viewing 15 posts - 16 through 30 (of 43 total)
You must be logged in to reply to this topic. Login to reply