another trigger question

I have a table named userInfo which has fields userID (uses Identity to fill
in), firstname, familyname.
I have a second table named administration which has fields userID and
userName.
I would like to have a trigger which when a new record is added to userInfo
creates a record in the administration table using the same userID and
joining the familyname and firstname fields from UserInfo together to make
the userName field in administration.
Could someone help me with the syntax.
Thank you
June
Why do you need an extra table for this? You can create a view called
vAdministration which calls:
SELECT userID, username = firstName + familyName FROM userInfo
No reason to store the data twice!
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"June Macleod" <junework@.hotmail.com> wrote in message
news:Oz734RpPEHA.2996@.TK2MSFTNGP12.phx.gbl...
> I have a table named userInfo which has fields userID (uses Identity to
fill
> in), firstname, familyname.
> I have a second table named administration which has fields userID and
> userName.
> I would like to have a trigger which when a new record is added to
userInfo
> creates a record in the administration table using the same userID and
> joining the familyname and firstname fields from UserInfo together to make
> the userName field in administration.
> Could someone help me with the syntax.
> Thank you
> June
>
|||On Thu, 20 May 2004 19:03:45 +0100, June Macleod wrote:

>I have a table named userInfo which has fields userID (uses Identity to fill
>in), firstname, familyname.
>I have a second table named administration which has fields userID and
>userName.
>I would like to have a trigger which when a new record is added to userInfo
>creates a record in the administration table using the same userID and
>joining the familyname and firstname fields from UserInfo together to make
>the userName field in administration.
>Could someone help me with the syntax.
>Thank you
>June
Hi June,
I could, but first I'll advice you to drop the administration table and
create an administration view instead:
CREATE VIEW administration AS
SELECT userID, familyname + ', ' + firstname AS userName
FROM userInfo
go
Another option would be to (again) drop the administration table and add
userName as computed column in the userInfo table:
ALTER TABLE userInfo
ADD userName AS familyname + ', ' + firstname
go
But if you really want to use seperate tables and keep it current with
triggers, you'll need not one but three triggers:
CREATE TRIGGER ins_userInfo
ON userInfo
AFTER INSERT
AS
IF @.@.ROWCOUNT > 0
INSERT administration (userID, userName)
SELECT userID, familyname + ', ' + firstname
FROM inserted
go
CREATE TRIGGER upd_userInfo
ON userInfo
AFTER UPDATE
AS
IF @.@.ROWCOUNT > 0 AND (UPDATE(familyname) OR UPDATE(firstname))
UPDATE administration
SET userName = familyname + ', ' + firstname
WHERE userID IN (SELECT userID FROM inserted)
go
CREATE TRIGGER del_userInfo
ON userInfo
AFTER DELETE
AS
IF @.@.ROWCOUNT > 0
DELETE administration
WHERE userID IN (SELECT userID FROM deleted)
go
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||> But if you really want to use seperate tables and keep it current with
> triggers, you'll need not one but three triggers:
Yes, excellent point... not only getting the data there on insert, but
keeping the tables in sync.
|||First of all let me thank you very much for your help. It is much
appreciated.
I have taken on board your advice about dropping the administration table
and using a view instead.
However, I am still trying to get the trigger to work as it will be good
practice for me for future tables which will require this type of update.
The insert and delete triggers work well however I am having problems with
the update trigger.
Create Trigger dbo.userInfo_Trigger1
On dbo.userInfo
AFTER UPDATE
AS
IF @.@.ROWCOUNT > 0 AND (UPDATE(familyname) OR UPDATE(firstname))
UPDATE administration
SET userName = familyname + ', ' + firstname
WHERE userID IN (SELECT userID FROM inserted)
When I try to save the trigger it comes back with an error message "ADO
Error: Invalid column name 'familyname'. Invalid column name 'firstname'."
These are the correct names in the userInfo table.
I am making the assumption that the 'inserted' table (and likewise the
'deleted' table ) are temporary tables created during the edit process.
Does an inserted table get created when an update is taking place or only
when a new record is created?
Thanks again
June
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:9o3qa0piuc0mm1j2lpvle020joi75glis4@.4ax.com...
> On Thu, 20 May 2004 19:03:45 +0100, June Macleod wrote:

> I could, but first I'll advice you to drop the administration table and
> create an administration view instead:
> CREATE VIEW administration AS
> SELECT userID, familyname + ', ' + firstname AS userName
> FROM userInfo
> go
> Another option would be to (again) drop the administration table and add
> userName as computed column in the userInfo table:
> ALTER TABLE userInfo
> ADD userName AS familyname + ', ' + firstname
> go
> But if you really want to use seperate tables and keep it current with
> triggers, you'll need not one but three triggers:
> CREATE TRIGGER ins_userInfo
> ON userInfo
> AFTER INSERT
> AS
> IF @.@.ROWCOUNT > 0
> INSERT administration (userID, userName)
> SELECT userID, familyname + ', ' + firstname
> FROM inserted
> go
> CREATE TRIGGER upd_userInfo
> ON userInfo
> AFTER UPDATE
> AS
> IF @.@.ROWCOUNT > 0 AND (UPDATE(familyname) OR UPDATE(firstname))
> UPDATE administration
> SET userName = familyname + ', ' + firstname
> WHERE userID IN (SELECT userID FROM inserted)
> go
> CREATE TRIGGER del_userInfo
> ON userInfo
> AFTER DELETE
> AS
> IF @.@.ROWCOUNT > 0
> DELETE administration
> WHERE userID IN (SELECT userID FROM deleted)
> go
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
|||On Fri, 21 May 2004 11:14:02 +0100, June Macleod wrote:
Hi June,

>First of all let me thank you very much for your help. It is much
>appreciated.
>I have taken on board your advice about dropping the administration table
>and using a view instead.
Good - glad to hear that.

>However, I am still trying to get the trigger to work as it will be good
>practice for me for future tables which will require this type of update.
That's why I went on to give you the trigger code after advising against
it.

>The insert and delete triggers work well however I am having problems with
>the update trigger.
>Create Trigger dbo.userInfo_Trigger1
>On dbo.userInfo
>AFTER UPDATE
>AS
>IF @.@.ROWCOUNT > 0 AND (UPDATE(familyname) OR UPDATE(firstname))
>UPDATE administration
>SET userName = familyname + ', ' + firstname
>WHERE userID IN (SELECT userID FROM inserted)
>When I try to save the trigger it comes back with an error message "ADO
>Error: Invalid column name 'familyname'. Invalid column name 'firstname'."
>These are the correct names in the userInfo table.
My fault. When I wrote that trigger, I momentarily forgot that userName is
not in the same table as familyname and firstname. The UPDATE statement
should read
UPDATE administration
SET userName = (SELECT familyname + ', ' + firstname
FROM inserted
WHERE inserted.userID = administration.userID)
WHERE userID IN (SELECT userID FROM inserted)

>I am making the assumption that the 'inserted' table (and likewise the
>'deleted' table ) are temporary tables created during the edit process.
Though technically incorrect, you might as well think of it that way. (The
exact technical explanation is that deleted and inserted are not temporary
tables, but pseudo-tables - they never really exist, but their contents
are reconstructed from the log file every time they are needed. If you
have a trigger that refers to inserted and deleted a lot, you might gain
performance by explicitly copying the data from those pseudo-tables to
temporary tables).

>Does an inserted table get created when an update is taking place or only
>when a new record is created?
First, it's important to note that the inserted and deleted pseudo-tables
can only be referenced inside a trigger. Even a stored procedure that is
called from a trigger has no access to inserted or deleted.
If a trigger is started as a result of an INSERT statement, than the
deleted pseudo-table will always be empty; the inserted pseudotable
contains all rows inserted by the INSERT statement.
If a trigger is started as a result of a DELETE statement, than the
deleted pseudo-table will contain all rows that are deleted by the DELETE
statement; the inserted pseudotable will be empty.
If a trigger is started as a result of an UPDATE statement, the deleted
pseudotable will contain all rows that match the WHERE clause of the
update, with all data as it was BEFORE the update; the inserted
pseudotable will contain the same rows as they appear AFTER applying the
SET clause of the update. If the UPDATE statement changed the value of the
primary key column(s) (which is unfortunately allowed in SQL Server), it
can be quite hard to find out which row in inserted matches which row in
deleted.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

another trigger question

Is trigger based on table level or can I define it on certain fields level
against a table?
A trigger is defined on an INSERT, UPDATE or DELETE statement against a
table (in fact you can write a special kind (INSTEAD OF trigger) against
views now too). INSERTs & DELETEs work on an entire row so it doesn't make
much sense to do stuff at the column level there, but within a trigger on an
UPDATE operation you can define specific behaviour if certain columns on the
table are changed with the IF UPDATE() clause (it works with INSERT triggers
too but makes less sense). For example,
CREATE TRIGGER MyInsertTrigger on dbo.MyTable
FOR UPDATE AS
-- Check to see if a specific column has changed
if UPDATE(MyCol)
BEGIN
-- Do some SQL stuff in here because the column we're interested
in has changed
END
-- Do other general UPDATE trigger stuff
GO
SQL Books Online has more info on it in the "Creating and Maintaining
Databases" | "Enforcing Business Rules with Triggers" | "Creating a Trigger"
| "Programming Triggers" section.
HTH.
Cheers,
Mike
""Allen Iverson"" <no_spam@.bk.com> wrote in message
news:ud8HZU20EHA.3584@.TK2MSFTNGP11.phx.gbl...
> Is trigger based on table level or can I define it on certain fields level
> against a table?
>
sql

another trigger question

Is trigger based on table level or can I define it on certain fields level
against a table?A trigger is defined on an INSERT, UPDATE or DELETE statement against a
table (in fact you can write a special kind (INSTEAD OF trigger) against
views now too). INSERTs & DELETEs work on an entire row so it doesn't make
much sense to do stuff at the column level there, but within a trigger on an
UPDATE operation you can define specific behaviour if certain columns on the
table are changed with the IF UPDATE() clause (it works with INSERT triggers
too but makes less sense). For example,
CREATE TRIGGER MyInsertTrigger on dbo.MyTable
FOR UPDATE AS
-- Check to see if a specific column has changed
if UPDATE(MyCol)
BEGIN
-- Do some SQL stuff in here because the column we're interested
in has changed
END
-- Do other general UPDATE trigger stuff
GO
SQL Books Online has more info on it in the "Creating and Maintaining
Databases" | "Enforcing Business Rules with Triggers" | "Creating a Trigger"
| "Programming Triggers" section.
HTH.
--
Cheers,
Mike
""Allen Iverson"" <no_spam@.bk.com> wrote in message
news:ud8HZU20EHA.3584@.TK2MSFTNGP11.phx.gbl...
> Is trigger based on table level or can I define it on certain fields level
> against a table?
>

another trigger question

Is trigger based on table level or can I define it on certain fields level
against a table?A trigger is defined on an INSERT, UPDATE or DELETE statement against a
table (in fact you can write a special kind (INSTEAD OF trigger) against
views now too). INSERTs & DELETEs work on an entire row so it doesn't make
much sense to do stuff at the column level there, but within a trigger on an
UPDATE operation you can define specific behaviour if certain columns on the
table are changed with the IF UPDATE() clause (it works with INSERT triggers
too but makes less sense). For example,
CREATE TRIGGER MyInsertTrigger on dbo.MyTable
FOR UPDATE AS
-- Check to see if a specific column has changed
if UPDATE(MyCol)
BEGIN
-- Do some SQL stuff in here because the column we're interested
in has changed
END
-- Do other general UPDATE trigger stuff
GO
SQL Books Online has more info on it in the "Creating and Maintaining
Databases" | "Enforcing Business Rules with Triggers" | "Creating a Trigger"
| "Programming Triggers" section.
HTH.
Cheers,
Mike
""Allen Iverson"" <no_spam@.bk.com> wrote in message
news:ud8HZU20EHA.3584@.TK2MSFTNGP11.phx.gbl...
> Is trigger based on table level or can I define it on certain fields level
> against a table?
>

Another Time Dimension question

I want to track sales vs. both date scheduled to be shipped and date actually shipped. Both are fields in my OLTP. I can set the SSIS program to extract data any why I choose.

Would it be best to create a fact table and 2 different dimension tables or combine the time fields in one dimension table or leave them in the fact table and let SQL Server extract the time dimension?

Thanks.

Hello! In SSAS2005(Analysis services 2005) you have something called 'Role-playing' dimensions that solves this problem. You will only need one time dimension in the starschema that you join to all dates in the fact table.

If you have these relations designed in the source system/star schema or in the data source view SSAS2005 will detect this relation and create separate time dimensions automatically when you build the cube.

HTH

Thomas Ivarsson

|||I don't want to apply one time dimension to many fact tables. I want to apply many time dimensions to one fact table. Right now my date fields are in the Fact Table.|||

I do not think that I have said that but my explanation, perhaps, was not good enough.

You use one time dimension and join the fact tables different date dimensions to the same date key in the time dimension table.

Connect the key for order date in the fact table to the date key in the time dimension. Connect the date key for invoice date in the fact table to the date key in the time dimension.

HTH

Thomas Ivarsson

|||

Ahh...that's better....I think I need to take my dates out of my fact table and create a dimTime table and then I can create the multiple relationships.

Thanks.

|||

That seems to be working but I'm a little fuzzy on the theory.

Either or both date fields may be null in my OLTP table, so I can't use either for the key field. My OLTP table has an integer key that is just a counting number.

I created the dimTime table with the same type key field. When I did my SSIS run I just copied the Fact table key field to the dimTime table key field along with the date fields.

The tables are linked but not throught a date field. SSAS is smart enough to build its aggregates on the date fields and pretty much ignore the key field? It only cares that the tables are linked somehow?

Thanks!!!!

|||

You can add a theoretical time member to the time dimension like '2099-12-31" and point fact records without time members to that time member.

There will be many more SQL Server releases until we reach that date.

HTH

/Thomas Ivarsson

Another SUM limitation problem

I am trying to simply do a rather simply you'd think.... SUM in my textbox on 3 COUNT fields in 3 tables...it's not possible, I get errors whatever way I try this. Look at the Grand Total, that's what I want....a sum of the 3 black fields that have COUNT in them. If this is not possible in SSRS 2005, then Microsoft missed something huge that totally degrades their entire platform, this sucks!

I'm so sick and tired in the past 2 months, very spent on battling SSRS 2005 limitations on Grand Totals like this on many occasions both in tables and freeform textboxes. If you can't do an aggregate on an aggregate then this interface is useless!

http://www.webfound.net/grand_total.jpg

You should be able to do this with a workaround and Code. On each count row, add a hidden cell that assigns a value to a private variable. In your text box, run the code to add up your values.

You should be able to do this with an array. But here is a quick and dirty, assuming you only have two count fields. For simplicity, they are called item1 and item2.

So the cell next to your first count would call code.SetItem1(ReportItems!item1.Value)

The next one would reference the SetItem2 function.

Your textbox then calls GrandTotal()

Code behind:

Dim private _item1 as Integer
Dim private _item2 as Integer

Public Function SetItem1(ByVal item1 As Object) As Integer

_item1 =item1
End Function

Public Function SetItem2(ByVal item2 As Object) As Integer

_item2 =item2
End Function

Public Function GrandTotal() as Object

Return _item1 + _item2

End Function

Another Query Question

Suppose you have a table in which one of the fields can have any
number of the same values. Is there a way to select on that field for
only those records where there is a single occurrence of that value in
the entire table ? I don't want any records returned by the query if
there is more than one occurrence, just if there's one. Thanks all.

Rick."Rick" <snarfie.mcdougal@.comcast.net> wrote in message
news:7b5ae645.0312110640.3eec56c4@.posting.google.c om...
> Suppose you have a table in which one of the fields can have any
> number of the same values. Is there a way to select on that field for
> only those records where there is a single occurrence of that value in
> the entire table ? I don't want any records returned by the query if
> there is more than one occurrence, just if there's one. Thanks all.
> Rick.

This is one way to do it, using the Northwind database - find any customers
who have only one row in the Orders table:

select * from
Orders t join
(
select CustomerID
from Orders
group by CustomerID
having count(*) = 1
) dt
on t.CustomerID = dt.CustomerID

Simon|||snarfie.mcdougal@.comcast.net (Rick) wrote in message news:<7b5ae645.0312110640.3eec56c4@.posting.google.com>...
> Suppose you have a table in which one of the fields can have any
> number of the same values. Is there a way to select on that field for
> only those records where there is a single occurrence of that value in
> the entire table ? I don't want any records returned by the query if
> there is more than one occurrence, just if there's one. Thanks all.
> Rick.
Hi Rick,

Are you talking about duplicate records? "Values" and "occurences"
are a little ambiguous. -- Louis

create table #T(x int)
insert into #T values(1)
insert into #T values(2)
insert into #T values(2)
insert into #T values(3)
insert into #T values(3)
insert into #T values(3)

select x
from #T
group by x
having count(*)=1

returns:
x
----
1|||snarfie.mcdougal@.comcast.net (Rick) wrote in message news:<7b5ae645.0312110640.3eec56c4@.posting.google.com>...
> Suppose you have a table in which one of the fields can have any
> number of the same values. Is there a way to select on that field for
> only those records where there is a single occurrence of that value in
> the entire table ? I don't want any records returned by the query if
> there is more than one occurrence, just if there's one. Thanks all.
> Rick.

To find single occurrances for a column...

select col1
count(*) as col_cnt
from table1
group by col1
having count(*) = 1;

so to return the rows with single occurances, join the above back to
the original table...

select t.*
from table 1 as t
(select col1
count(*) as col_cnt
from table1
group by col1
having count(*) = 1
) as s
where t.col1 = s.col1;

Christian.|||ok, forgive me cause I'm doing this strictly out of memory, but its
close...

Select col1, count(*)
from mytable
group by col1
having count(*) = 1

or

declare @.tResults TABLE (mycol int, rowcount int)
insert into @.tResults
Select col1, count(*)
from mytable
group by col1
having count(*) = 1

select mycol from @.tResults where rowcount = 1

"Rick" <snarfie.mcdougal@.comcast.net> wrote in message
news:7b5ae645.0312110640.3eec56c4@.posting.google.c om...
> Suppose you have a table in which one of the fields can have any
> number of the same values. Is there a way to select on that field for
> only those records where there is a single occurrence of that value in
> the entire table ? I don't want any records returned by the query if
> there is more than one occurrence, just if there's one. Thanks all.
> Rick.

Another query problem

I am using Dreamweaver for the following:
Hi,
I have a problem with a search/results page, one of the fields is a date, I
want to display all results if the date is not entered in the search page
and display the records that match the date entered if any.
My problem is that if in the results page recordset I enter:
Lastworked = 'MMColParam7'
and I enter MMColParam7 01/01/1950 Request("Date")
Then the result is that if the date is entered I DO get the correct results,
but if the date is not entered I don't get any results at all becasue it
takes 01/01/1950 as default date.
Then I try to use
Lastworked LIKE 'MMColParam7'
and I enter MMColParam7 % Request("Date")
Then If i enter the date I get NO results, even where there are records that
match.
Any ideas ?
ACould you enter more info... Like the table structures of the two SQL tables
you are referring to, and the exact SQL Query you are sending to the databas
e
that is producing "incorrect" results? Some parts of Your post, specificall
y
Lastworked = 'MMColParam7'
are confusing. Lastworked sounds like a name for a column with dates in it,
but 'MMColParam7' is a string, not a date, and IT sopunds like the name for
another column...
"Aleks" wrote:

> I am using Dreamweaver for the following:
> Hi,
> I have a problem with a search/results page, one of the fields is a date,
I
> want to display all results if the date is not entered in the search page
> and display the records that match the date entered if any.
> My problem is that if in the results page recordset I enter:
> Lastworked = 'MMColParam7'
> and I enter MMColParam7 01/01/1950 Request("Date")
> Then the result is that if the date is entered I DO get the correct result
s,
> but if the date is not entered I don't get any results at all becasue it
> takes 01/01/1950 as default date.
>
> Then I try to use
> Lastworked LIKE 'MMColParam7'
> and I enter MMColParam7 % Request("Date")
> Then If i enter the date I get NO results, even where there are records th
at
> match.
> Any ideas ?
>
> A
>
>

and / or

Hi,
Can you see if this query is correct?
I would like to make sure the select query returns fieldID only if any of the fields inside the bracket (field4, 5, 6, 7) has changed.

--has anything for this record changed?...
--if so, add it into the Differences table...
select
fieldID
from
tblTable
where
fieldID = @.fieldID and
fieldID2 = @.NB
and
( field3 != @.field3 or field4 != @.field4
or field5 != @.field5
or field6 != @.field6
or field7 != @.field7
)This will work if all of your fields do not allow null; however, you will need to test for nulls if your columns allow for nulls.|||

Some of the fields do allow for nulls.

You mean I should include nullif ?

|||

You will need to change

Code Snippet

field3 != @.field3

into

Code Snippet

field3 != @.field3 or field3 is null and @.field3 is not null or field3 is not null and @.field3 is null

You will need to do this or something similar using ISNULL or COALESCE for all of your fields that you are testing for not equal.

|||

You could use the CHECKSUM() function to compare the left and right sides of the WHERE clause.

As this example demonstrates, data conversions are not required and null values will also work.

Code Snippet

DECLARE
@.Param1 varchar(20),
@.Param2 varchar(20),
@.Param3 int,
@.Param4 int,
@.NullValue varchar(10)

SELECT
@.Param1 = 'Test',
@.Param2 = 'Test2',
@.Param3 = 25

IF checksum( @.Param1, @.Param2, @.Param3, @.Param4 ) = checksum( 'Test', 'Test2', 25, @.NullValue )
PRINT 'Match'
ELSE
PRINT 'NoMatch'

So for your needs, you would use:

AND checksum( Field3, Field4, Field5) <> checksum( @.Field3, @.Field4, @.Field5)

(List shortened for display purposes...)

|||

Arnie Rowland wrote:

You could use the CHECKSUM() function to compare the left and right sides of the WHERE clause.

Please don't use CHECKSUM or BINARY_CHECKSUM functions. They are not guaranteed to produce unique values for input. They are simple hash functions used to divide set of values into different ranges (for example to create compact indexes or partition the data). In fact, with the current implementation you can get duplicate checksum values quite easily and there are certain types of input values that will simply produce unexpected results (repeated values, NULLs etc). You could use hashbytes in SQL Server 2005 which can generate MD5 or MD4 hash for example which can avoid collisions but still no guarantee to produce unique value for each input.

|||

You can change your WHERE clause to below since indexes if any on the columns (those with the OR checks) will not really help.

and
( coalesce(field3, '') <> coalesce(@.field3, '') -- use appriate data type value '' or 0 and so on.

-- repeat for others

or field4 != @.field4
or field5 != @.field5
or field6 != @.field6
or field7 != @.field7
)