variable with select - 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

SQL Server 2000 Forums

Transact-SQL (2000)

variable with select

Author

Topic

rstout
Starting Member

25 Posts

Posted - 2005-04-01 : 15:00:54

I need to use a variable in a select statement. This is what I have so is there a way to make it work.

DECLARE @AP VARCHAR(2)

-- Get the record field named "A" with a number between 0 and 9 ie:"A1"
SELECT @AP = 'A' + (SELECT J FROM LOC WHERE CD = (SELECT SC FROM Inserted))

-- Here is the problem
IF NOT UPDATE(@AP) OR (SELECT AL FROM Deleted) != ''
BEGIN
RETURN
END

--Here too
-- Copy the A1 value to AL.
UPDATE ATT
SET AL = (SELECT @AP FROM Inserted)
FROM ATT t join inserted i
--- bla bla....

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-04-01 : 15:28:55

Please see my trigger tip about using variables in triggers:
http://weblogs.sqlteam.com/tarad/archive/2004/09/14/2077.aspx

So you'll not want to use variables as that'll require you to act on each row one at a time which will be slow and cause the transaction to wait for it to complete.

Also, your code is confusing so I'm not sure exactly what you are trying to do. Otherwise, I would have posted some additional help. I'm just not clear what you want this trigger to do.

Tara

rstout
Starting Member

25 Posts

Posted - 2005-04-01 : 16:34:01

In table att to I want to copy the value in A1 to AL. The problem I have to check table LOC for a value and concanatate it with the letter A so it will look like A1 or A4. I did not want to hard code it in. See my trigger below yours

I am even more confused after reading your tip. Where do you come up with "Update t2"

CREATE TRIGGER trg_Table1
ON Table1
FOR UPDATE
AS

UPDATE t2
SET SomeColumn = t1.SomeColumn
FROM Table2 t2
INNER JOIN inserted i
ON t2.Table1_ID = i.Table1_ID

CREATE TRIGGER ABI_Period_Attednance_For_Daily_Attendance_Reporting3
ON ATT
FOR INSERT, UPDATE
AS

DECLARE @AttendPeriod VARCHAR(2)

-- Exit if the school is configured for period attendance
IF (SELECT A FROM LOC WHERE CD = (SELECT SC FROM Inserted)) = '1'
BEGIN
RETURN
END

-- Get attendance period from LOC.
SELECT @AttendPeriod = 'A' + (SELECT J FROM LOC WHERE CD = (SELECT SC FROM Inserted))

-- Exit if A1 has not been updated or AL has a value.
IF NOT UPDATE('A' + @AttendPeriod) OR (SELECT AL FROM Deleted) != ''
BEGIN
RETURN
END

-- Copy the A1 value to AL.
UPDATE ATT
SET AL = (SELECT A + @AttendPeriod FROM Inserted)
FROM ATT t join inserted i
ON t.SC = i.SC AND
t.SN = i.SN AND
t.DY = i.DY
WHERE t.SC = i.SC AND
t.SN = i.SN AND
t.DY = i.DY
RETURN

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-04-01 : 16:43:10

t2 is just an alias for Table2. So instead of referring to it as its name, we use aliases to make it easier to read.

Tara

rstout
Starting Member

25 Posts

Posted - 2005-04-01 : 16:59:09

So...

The trigger I am creating uses SET like in your example. So the following
-- Exit if the school is configured for period attendance
IF (SELECT A FROM LOC WHERE CD = (SELECT SC FROM Inserted)) = '1'
BEGIN
RETURN
END

-- Get attendance period from LOC.
SELECT @AttendPeriod = 'A' + (SELECT J FROM LOC WHERE CD = (SELECT SC FROM Inserted))

-- Exit if A1 has not been updated or AL has a value.
IF NOT UPDATE('A' + @AttendPeriod) OR (SELECT AL FROM Deleted) != ''
BEGIN
RETURN
END

will only check the last record of the inserted and deleted?

But the following will be the set-based solution?
-- Copy the A1 value to AL.
UPDATE ATT
SET AL = (SELECT A + @AttendPeriod FROM Inserted)
FROM ATT t join inserted i
ON t.SC = i.SC AND
t.SN = i.SN AND
t.DY = i.DY
WHERE t.SC = i.SC AND
t.SN = i.SN AND
t.DY = i.DY
RETURN

I need the first part to run on each record updated or inserted.

I noticed you are THE Almighty SQL Goddess, so I humbly come to you for wisdom.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-04-01 : 17:03:19

Here is where the problem is:

SELECT @AttendPeriod = 'A' + (SELECT J FROM LOC WHERE CD = (SELECT SC FROM Inserted))

You are only operating on one row. This is the problem with variables. They contain one value at a time. And you haven't done any looping, which wouldn't be advisable anyway, so you are only operating on one row with @AttendPeriod.

In order for us to help better, we'd need DDL for your ATT and LOC tables as well as INSERT INTO statements for sample data, sample INSERT statement that would cause the trigger to fire, and the expected result set of the ATT table after the INSERT statement runs and after the trigger fires. It's just too hard to figure out what is going on with your trigger without us testing it out which we can not do without this information.

Tara

rstout
Starting Member

25 Posts

Posted - 2005-04-01 : 17:27:21

I am not sure how you want the DDL? Will it work if I make a script from the table?

Here is the before and after with a simple insert statement, I am checking on how the code adds the data.

insert att
(A1, sc, sn, dy) values ('E', 10, 2420, 225)

ATT Table After Insert before trigger

SC SN DY CD PR GR TR TN AL A0 A1 A2 A3 A4 A5 A6 A7 A8 A9 DT RS NS DEL DTS AP1 AP2
------ ----------- ------ ---- ---- ------ ---- ------ ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------------------------------------------------------ ---- ----------- ---- ------------------------------------------------------ ---- ----
10 2420 225 0 0 E g NULL 0 0 2005-04-01 10:09:49.140

ATT Table After Update before trigger

SC SN DY CD PR GR TR TN AL A0 A1 A2 A3 A4 A5 A6 A7 A8 A9 DT RS NS DEL DTS AP1 AP2
------ ----------- ------ ---- ---- ------ ---- ------ ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------------------------------------------------------ ---- ----------- ---- ------------------------------------------------------ ---- ----
10 2420 225 0 0 E E g NULL 0 0 2005-04-01 10:09:49.140

rstout
Starting Member

25 Posts

Posted - 2005-04-01 : 17:30:16

Well that did not look right...

insert att
(A1, sc, sn, dy) values ('E', 10, 2420, 225)

ATT Table After Insert before trigger
SC SN DY CD PR GR TR TN AL A0 A1 A2 A3 A4 A5
------ ----------- ------ ---- ---- ------ ---- ------ ---- ---- ---- ---- ---- ---- ----
10 2420 225 0 0 E g

ATT Table After Update before trigger
SC SN DY CD PR GR TR TN AL A0 A1 A2 A3 A4 A5
------ ----------- ------ ---- ---- ------ ---- ------ ---- ---- ---- ---- ---- ---- ----
10 2420 225 0 0 E E g

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-04-01 : 17:30:48

What you have posted, we can not use. Let me show you an example of a question I posted today and how we need the information. You see how quickly I get answers when I post an example that can be copied and pasted into someone else's environment?

Here's the post:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47870

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-04-01 : 17:33:19

Oh geez! I just realized what you are trying to accomplish is due to your database design. Your database is not normalized. You are trying to get a column name and attach a value to it so you don't need the repeated code, right? This isn't going to work without quite a bit of work.

Normalize your database! And if you don't know what that means, look it up as it isn't easy to explain in a thread.

Tara

rstout
Starting Member

25 Posts

Posted - 2005-04-01 : 17:34:36

Well how about a text file?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-04-01 : 17:37:07

Your problem is not easily solvable given your database design. What you are trying to accomplish isn't going to work without quite a bit of work. Do yourself a favor and normalize your database and you won't have this problem with the A columns.

Tara

rstout
Starting Member

25 Posts

Posted - 2005-04-01 : 17:46:15

OK if I don't go all fancy with variables and hard code it would the following work?

CREATE TRIGGER ABI_Period_Attednance_For_Daily_Attendance_Reporting3
ON ATT
FOR INSERT, UPDATE
AS

-- Exit if A1 has not been updated or AL has a value.
IF NOT UPDATE(A1) OR (SELECT AL FROM Deleted) != ''
BEGIN
RETURN
END

-- Copy the A1 value to AL.
UPDATE ATT
SET AL = (SELECT A1 FROM Inserted)
FROM ATT t join inserted i
ON t.SC = i.SC AND
t.SN = i.SN AND
t.DY = i.DY
WHERE t.SC = i.SC AND
t.SN = i.SN AND
t.DY = i.DY
RETURN

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-04-01 : 17:49:38

This is probably what you want for the UPDATE statement.

-- Copy the A1 value to AL.
UPDATE t
SET AL = i.A1
FROM ATT t
INNER JOIN inserted i
ON t.SC = i.SC AND
t.SN = i.SN AND
t.DY = i.DY

Tara

rstout
Starting Member

25 Posts

Posted - 2005-04-01 : 17:58:07

INNER JOIN saved some code, cool.

so will the following work on each record or just the last record in the inserted table like in your trigger tip?

-- Exit if A1 has not been updated or AL has a value.
IF NOT UPDATE(A1) OR (SELECT AL FROM Deleted) != ''
BEGIN
RETURN
END

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-04-01 : 18:11:31

I believe the second part will not work (OR (SELECT AL FROM Deleted) != ''). The deleted table potentially contains more than one row. I think what you want is:

IF NOT UPDATE(A1) OR NOT EXISTS (SELECT * FROM Deleted WHERE AL != '')

Always test this on a test system first though. I'm unsure if you want EXISTS or NOT EXISTS.

Tara

rstout
Starting Member

25 Posts

Posted - 2005-04-01 : 18:47:25

Amen on the test enviroment!!

But if there is more than one row and only one of the rows has a value then whole thing will exit, right? I need to check each row for that value.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-04-01 : 18:53:01

Yep, you are right. Sorry about that. What I think you want is this:

IF NOT UPDATE(A1)
BEGIN
RETURN
END

-- Copy the A1 value to AL.
UPDATE t
SET AL = i.A1
FROM ATT t
INNER JOIN inserted i
ON t.SC = i.SC AND
t.SN = i.SN AND
t.DY = i.DY
INNER JOIN deleted d
ON...<put the join condition here>
WHERE d.AL = ''

Tara

rstout
Starting Member

25 Posts

Posted - 2005-04-01 : 19:05:06

Forgive me for being such a newbie, so it would end up looking like this?

IF NOT UPDATE(A1)
BEGIN
RETURN
END

-- Copy the A1 value to AL.
UPDATE t
SET AL = i.A1
FROM ATT t
INNER JOIN inserted i
ON t.SC = i.SC AND
t.SN = i.SN AND
t.DY = i.DY
INNER JOIN deleted d
ON t.SC = d.SC AND
t.SN = d.SN AND
t.DY = d.DY
WHERE d.AL = ''

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-04-01 : 19:08:14

Are those three columns the primary key of the ATT table? If so, then yes. If not, well what is the PK of the ATT table?

Tara

rstout
Starting Member

25 Posts

Posted - 2005-04-01 : 19:13:10

yes they are the keys but no workie workie

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources