Complex Script as a Trigger - SQL Server Forums

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

Complex Script as a Trigger

Author

Topic

tedeansiii
Starting Member

2 Posts

Posted - 2012-07-05 : 11:58:21

I am a new Database Administrator but i have a some mediocre knowledge of ASP, VBScript programming an have dabbled a little bit with SQL, mysql and powershell, So some things I do know. Triggers are not one of them. Our company is changing our main system for everything and we needed an old RPG program which my boss wrote to be converted to SQL. I got the task. I have finished the script but the catch is it needs to be in the form of a trigger. Basically we have a table which has custom columns, two of which one is a sequence number which is a default of a 1 for everyone, the second is a barcode number which we create from their user id and the sequence number. we need the trigger to update the barcode number anytime the sequence number changes.

Can anyone help me with this? I have read about triggers and tried to get it to work as a trigger with no luck.

Below is the script:

Can someone help me with turning this into a trigger? I have never written a trigger so this will be my first one. Thanks

--------- Begin Code ---------

--Platform: Microsoft SQL Server Script
--Purpose Written: Calculate Barcode for Aramark Systems ID Cards.
--Written by: Thomas E. Deans III
--Date Written: 6-10-2012

declare @seq_num as int
-- Pull the newly inserted/updated sequence number from the
-- udf table it is being-- updated/inserted into when triggered by -- -- change. Update the barcode via this trigger
set @seq_num = ((select inserted.BCN_BAR_CDE_SEQ from TmsEPrd..NAME_MASTER_UDF nmu join inserted i on nmu.BCN_BAR_CDE = i.BCN_BAR_CDE) * 10)

-- Set variable equal to base number
declare @14_long as bigint
set @14_long = 20000000000000

-- Pull id num from current row being updated by trigger
declare @id_num as bigint
set @id_num = ((select inserted.ID_NUM from tmseprd..NAME_MASTER_UDF nmu join inserted i on nmu.ID_NUM = i.id_num where len(ID_NUM) >= 6 and LEN(id_num) < 7) * 10000)

-- Add up for the base number = base number + calculated id num + calculated sequence number
declare @temp as bigint
declare @temp_2 as bigint
set @temp = (@14_long + @id_num + @seq_num)
set @temp_2 = @temp

-- Declare variables for digits 02, 04, 06, 08, 10 & 12 of the 14 digit @temp number,
-- and store them in variables.
declare @var_pos_02 as int
declare @var_pos_04 as int
declare @var_pos_06 as int
declare @var_pos_08 as int
declare @var_pos_10 as int
declare @var_pos_12 as int
declare @even_total as int

-- Setting values for declared variables.
set @var_pos_02 = right(left(@temp_2,2),1)
set @var_pos_04 = right(left(@temp_2,4),1)
set @var_pos_06 = right(left(@temp_2,6),1)
set @var_pos_08 = right(left(@temp_2,8),1)
set @var_pos_10 = right(left(@temp_2,10),1)
set @var_pos_12 = right(left(@temp_2,12),1)
set @even_total = (@var_pos_02 + @var_pos_04 + @var_pos_06 + @var_pos_08 + @var_pos_10 + @var_pos_12)

-- Declare variables for digits 01, 03, 05, 07, 09, 11, & 13 of the 14 digit @temp number,
-- and store them in variables.
declare @var_pos_01 as int
declare @var_pos_03 as int
declare @var_pos_05 as int
declare @var_pos_07 as int
declare @var_pos_09 as int
declare @var_pos_11 as int
declare @var_pos_13 as int

-- Setting values for declared variables.
set @var_pos_01 = right(left(@temp_2,1),1)
set @var_pos_03 = right(left(@temp_2,3),1)
set @var_pos_05 = right(left(@temp_2,5),1)
set @var_pos_07 = right(left(@temp_2,7),1)
set @var_pos_09 = right(left(@temp_2,9),1)
set @var_pos_11 = right(left(@temp_2,11),1)
set @var_pos_13 = right(left(@temp_2,13),1)

-- Declare variables to work with individual odd digits.
declare @odd_register_01 as int
declare @odd_register_03 as int
declare @odd_register_05 as int
declare @odd_register_07 as int
declare @odd_register_09 as int
declare @odd_register_11 as int
declare @odd_register_13 as int

-- Set values for all odd variable digits.
set @odd_register_01 = (@var_pos_01 * 2)
set @odd_register_03 = (@var_pos_03 * 2)
set @odd_register_05 = (@var_pos_05 * 2)
set @odd_register_07 = (@var_pos_07 * 2)
set @odd_register_09 = (@var_pos_09 * 2)
set @odd_register_11 = (@var_pos_11 * 2)
set @odd_register_13 = (@var_pos_13 * 2)

-- Declare variables to work with left and right digits of odd digits in barcode in case statements two sections down.
declare @odd_register_01_tmp as int
declare @odd_register_03_tmp as int
declare @odd_register_05_tmp as int
declare @odd_register_07_tmp as int
declare @odd_register_09_tmp as int
declare @odd_register_11_tmp as int
declare @odd_register_13_tmp as int

-- Declare variables to total left and right didit of odd digits in barcode number.
declare @odd_register_01_tot as int
declare @odd_register_03_tot as int
declare @odd_register_05_tot as int
declare @odd_register_07_tot as int
declare @odd_register_09_tot as int
declare @odd_register_11_tot as int
declare @odd_register_13_tot as int

-- Set the odd tmp variable equal to the odd digit of the barcode.
-- Example of what we're doing here: @odd_register_01 might be a 9, (9*2) = 18. We are adding the number in the first digit to the
-- number in the second digit for calculating a check digit. The first digit is 1, the second is 8,
-- since the whole number in @odd_register_01 can never be above 18 we check the first digits number for a 1, anything else will
-- always be an even number because the number you start with is multiplied by 2. A single digit in any mathematical placeholder
-- can never be larger than a 9, the lowest number we can have is 0, if there is not a 1 in the first digit it could contain a
-- 2, 4, 6, or 8, and the highest we can have is 18. (example 9 multiplied by 2). If the first digit contains a 1 we want to add
-- the first digit to the second, if it's not a 1 then we want to just keep the value in the first digits placeholder.
-- -----------------------------------------------------------------------------------------------------------------------------
-- Case Statement: In @odd_register_01 give me the left digit, check for 1, if 1 then add the first digit plus second digit, else
-- give me first digit
-- required the use of a (cast as int) because even though we are working with numbers left() and right() are varchar functions.
-- trying to add the left digit and right digit without cast as int returned 44 for an @odd_register_ of 4 instead of 8 and 18 instead of 9.
set @odd_register_01_tmp = @odd_register_01
set @odd_register_01_tot = case when cast(left(@odd_register_01,1) as int) = 1 then ((cast(left(@odd_register_01,1) as int)) + (cast(RIGHT(left(@odd_register_01,2),1) as int))) else cast(left(@odd_register_01,1) as int) end
set @odd_register_03_tmp = @odd_register_03
set @odd_register_03_tot = case when cast(left(@odd_register_03,1) as int) = 1 then ((cast(left(@odd_register_03,1) as int)) + (cast(RIGHT(left(@odd_register_03,2),1) as int))) else cast(left(@odd_register_03,1) as int) end
set @odd_register_05_tmp = @odd_register_05
set @odd_register_05_tot = case when cast(left(@odd_register_05,1) as int) = 1 then ((cast(left(@odd_register_05,1) as int)) + (cast(RIGHT(left(@odd_register_05,2),1) as int))) else cast(left(@odd_register_05,1) as int) end
set @odd_register_07_tmp = @odd_register_07
set @odd_register_07_tot = case when cast(left(@odd_register_07,1) as int) = 1 then ((cast(left(@odd_register_07,1) as int)) + (cast(RIGHT(left(@odd_register_07,2),1) as int))) else cast(left(@odd_register_07,1) as int) end
set @odd_register_09_tmp = @odd_register_09
set @odd_register_09_tot = case when cast(left(@odd_register_09,1) as int) = 1 then ((cast(left(@odd_register_09,1) as int)) + (cast(RIGHT(left(@odd_register_09,2),1) as int))) else cast(left(@odd_register_09,1) as int) end
set @odd_register_11_tmp = @odd_register_11
set @odd_register_11_tot = case when cast(left(@odd_register_11,1) as int) = 1 then ((cast(left(@odd_register_11,1) as int)) + (cast(RIGHT(left(@odd_register_11,2),1) as int))) else cast(left(@odd_register_11,1) as int) end
set @odd_register_13_tmp = @odd_register_13
set @odd_register_13_tot = case when cast(left(@odd_register_13,1) as int) = 1 then ((cast(left(@odd_register_13,1) as int)) + (cast(RIGHT(left(@odd_register_13,2),1) as int))) else cast(left(@odd_register_13,1) as int) end

-- Declare calculated variables.
declare @total as bigint
declare @processed_odd_total as bigint
declare @total_2 as bigint
declare @final_total as bigint

-- Add processed odd variables together for a total odd digit sum number.
set @processed_odd_total = (@odd_register_01_tot + @odd_register_03_tot + @odd_register_05_tot + @odd_register_07_tot + @odd_register_09_tot + @odd_register_11_tot + @odd_register_13_tot)
-- Add total for sum of odd and sum of even
set @total = (@even_total + @processed_odd_total)
-- The total for sum of odd + sum of even can be a 3 digit number,
-- so we want the right most digit 1 digit to the left in 3 digit number.
set @total_2 = right(left(@total,3),1)
-- Set the final calculated sequence number to be 10 minus the last number we calculated.
set @final_total = (10 - @total_2)
-- Set the final calculated id_num to be equal to the last number plus the original @temp.
set @temp = (@temp_2 + @final_total)
Print ' '
print 'Final Barcode Number'
-- Update the field in the bcn_name_master_udf for id_num.
update TmsEPrd..NAME_MASTER_UDF nmu insert @temp into bcn_bar_cde join inserted i on nmu.id_num = i.id_num

--------- End Code ---------

I know that some of the lines that deal with setting a variable aren't correct, i wrote them pseudo so you could understand what I was trying to do.

The subtle is often profound.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-05 : 12:10:08

why dont make barcodenumber as a computed column. Attach a default constraint to sequence to make it 1 always if not entered otherwise. then you can dispense with trigger as such.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

tedeansiii
Starting Member

2 Posts

Posted - 2012-07-05 : 16:23:51

I am not sure i understand what you are saying. I already have the sequence number column set to have a default value of 1. We cannot use a computed column because we need the data for later user by two other systems.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-05 : 16:28:03

quote:
Originally posted by tedeansiii

I am not sure i understand what you are saying. I already have the sequence number column set to have a default value of 1.

The subtle is often profound.

then you're halfdone
just make barcode as a computed column based on existing sequence column

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-07-05 : 16:53:31

quote:
We cannot use a computed column because we need the data for later user by two other systems.

A computed column is part is no different from a fixed column as far as other applications are concerned, it's still part of the table.

It looks like you're doing a Mod 10 / Luhn check digit calculation. If that's the case, there are user-defined functions here that you can utilize in a computed column:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76195&whichpage=1

Take a look at the latter versions on the first page.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources