Creating DB from scripts: update

Showing posts with label update. Show all posts

Thursday, March 29, 2012

Creating an INSERT UPDATE Trigger that works on certain account

Hi folks,
I have to create an INSERT UPDATE Trigger that pushes data from MS SQL 2000
to another proprietary database server. The other database server will
occassionally push data back into MS SQL via JDBC. I don't want the MS SQL
trigger to execute its SQL code if the INSERT UPDATE Trigger was initiated by
the proprietary database account. How do I capture the user or group name in
MS SQL? I know in PostgreSQL, you can use the "user" variable in a trigger.
What is the equivalent in MS SQL? Thanks in advance!!!just use user_name()
--
Venkat
sql server admirer
"Jaime Rios" wrote:
> Hi folks,
> I have to create an INSERT UPDATE Trigger that pushes data from MS SQL 2000
> to another proprietary database server. The other database server will
> occassionally push data back into MS SQL via JDBC. I don't want the MS SQL
> trigger to execute its SQL code if the INSERT UPDATE Trigger was initiated by
> the proprietary database account. How do I capture the user or group name in
> MS SQL? I know in PostgreSQL, you can use the "user" variable in a trigger.
> What is the equivalent in MS SQL? Thanks in advance!!!|||This is a multi-part message in MIME format.
--=_NextPart_000_0EFB_01C6952D.3CD86780
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
Try the sytem function: system_user.
For example, SELECT system_user will provide the Domain\Loginname of the =currently logged in user account.
Look in Books-on-Line for more information on system_user.
-- Arnie Rowland, YACE* "To be successful, your heart must accompany your knowledge."
*Yet Another Certification Exam
"Jaime Rios" <JaimeRios@.discussions.microsoft.com> wrote in message =news:EB859EAF-3AC8-4CDC-8ECF-89A3038CC99A@.microsoft.com...
> Hi folks,
> I have to create an INSERT UPDATE Trigger that pushes data from MS SQL =2000 > to another proprietary database server. The other database server will =
> occassionally push data back into MS SQL via JDBC. I don't want the MS =SQL > trigger to execute its SQL code if the INSERT UPDATE Trigger was =initiated by > the proprietary database account. How do I capture the user or group =name in > MS SQL? I know in PostgreSQL, you can use the "user" variable in a =trigger. > What is the equivalent in MS SQL? Thanks in advance!!!
--=_NextPart_000_0EFB_01C6952D.3CD86780
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Try the sytem function: system_user. =
For example, SELECT system_user will provide the =Domain\Loginname of the currently logged in user account.
Look in Books-on-Line for more =information on system_user.
-- Arnie Rowland, YACE* "To be =successful, your heart must accompany your knowledge."
*Yet Another Certification =Exam
"Jaime Rios" wrote in message news:EB859EAF-3AC8-4CDC-8ECF-89A3038CC99A@.microsoft.com...> =Hi folks,> I have to create an INSERT UPDATE Trigger that pushes data from MS SQL =2000 > to another proprietary database server. The other database =server will > occassionally push data back into MS SQL via JDBC. I don't want =the MS SQL > trigger to execute its SQL code if the INSERT UPDATE =Trigger was initiated by > the proprietary database account. How do I capture =the user or group name in > MS SQL? I know in PostgreSQL, you can use =the "user" variable in a trigger. > What is the equivalent in MS SQL? =Thanks in advance!!!

--=_NextPart_000_0EFB_01C6952D.3CD86780--|||A problem with using user_name() is that it will return 'dbo' for any user
that is in the dbOwner or SA roles. That may not be as helpful as
system_user. (no parens after system_user).
System_User provides the complete loggin DOMAIN\username. It is the best
option for any form of auditing and perhaps for the purpose you have in
mind.
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another Certification Exam
"Venkat" <Venkat@.discussions.microsoft.com> wrote in message
news:3F000F3F-7605-4D1E-BCD1-B9452F532943@.microsoft.com...
> just use user_name()
> --
> Venkat
> sql server admirer
>
> "Jaime Rios" wrote:
>> Hi folks,
>> I have to create an INSERT UPDATE Trigger that pushes data from MS SQL
>> 2000
>> to another proprietary database server. The other database server will
>> occassionally push data back into MS SQL via JDBC. I don't want the MS
>> SQL
>> trigger to execute its SQL code if the INSERT UPDATE Trigger was
>> initiated by
>> the proprietary database account. How do I capture the user or group name
>> in
>> MS SQL? I know in PostgreSQL, you can use the "user" variable in a
>> trigger.
>> What is the equivalent in MS SQL? Thanks in advance!!!|||I concur with Arnie.
--
Venkat
sql server admirer
"Arnie Rowland" wrote:
> Try the sytem function: system_user.
> For example, SELECT system_user will provide the Domain\Loginname of the currently logged in user account.
> Look in Books-on-Line for more information on system_user.
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another Certification Exam
>
> "Jaime Rios" <JaimeRios@.discussions.microsoft.com> wrote in message news:EB859EAF-3AC8-4CDC-8ECF-89A3038CC99A@.microsoft.com...
> > Hi folks,
> > I have to create an INSERT UPDATE Trigger that pushes data from MS SQL 2000
> > to another proprietary database server. The other database server will
> > occassionally push data back into MS SQL via JDBC. I don't want the MS SQL
> > trigger to execute its SQL code if the INSERT UPDATE Trigger was initiated by
> > the proprietary database account. How do I capture the user or group name in
> > MS SQL? I know in PostgreSQL, you can use the "user" variable in a trigger.
> > What is the equivalent in MS SQL? Thanks in advance!!!

Creating an INSERT UPDATE Trigger that works on certain account

just use user_name()
--
Venkat
sql server admirer
"Jaime Rios" wrote:

> Hi folks,
> I have to create an INSERT UPDATE Trigger that pushes data from MS SQL 200
0
> to another proprietary database server. The other database server will
> occassionally push data back into MS SQL via JDBC. I don't want the MS SQL
> trigger to execute its SQL code if the INSERT UPDATE Trigger was initiated
by
> the proprietary database account. How do I capture the user or group name
in
> MS SQL? I know in PostgreSQL, you can use the "user" variable in a trigger
.
> What is the equivalent in MS SQL? Thanks in advance!!!Try the sytem function: system_user.
For example, SELECT system_user will provide the Domain\Loginname of the cur
rently logged in user account.
Look in Books-on-Line for more information on system_user.
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Jaime Rios" <JaimeRios@.discussions.microsoft.com> wrote in message news:EB859EAF-3AC8-4CDC-
8ECF-89A3038CC99A@.microsoft.com...
> Hi folks,
> I have to create an INSERT UPDATE Trigger that pushes data from MS SQL 200
0
> to another proprietary database server. The other database server will
> occassionally push data back into MS SQL via JDBC. I don't want the MS SQL
> trigger to execute its SQL code if the INSERT UPDATE Trigger was initiated
by
> the proprietary database account. How do I capture the user or group name
in
> MS SQL? I know in PostgreSQL, you can use the "user" variable in a trigger
.
> What is the equivalent in MS SQL? Thanks in advance!!!|||A problem with using user_name() is that it will return 'dbo' for any user
that is in the dbOwner or SA roles. That may not be as helpful as
system_user. (no parens after system_user).
System_User provides the complete loggin DOMAIN\username. It is the best
option for any form of auditing and perhaps for the purpose you have in
mind.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Venkat" <Venkat@.discussions.microsoft.com> wrote in message
news:3F000F3F-7605-4D1E-BCD1-B9452F532943@.microsoft.com...[vbcol=seagreen]
> just use user_name()
> --
> Venkat
> sql server admirer
>
> "Jaime Rios" wrote:
>|||Hi folks,
I have to create an INSERT UPDATE Trigger that pushes data from MS SQL 2000
to another proprietary database server. The other database server will
occassionally push data back into MS SQL via JDBC. I don't want the MS SQL
trigger to execute its SQL code if the INSERT UPDATE Trigger was initiated b
y
the proprietary database account. How do I capture the user or group name in
MS SQL? I know in PostgreSQL, you can use the "user" variable in a trigger.
What is the equivalent in MS SQL? Thanks in advance!!!|||just use user_name()
--
Venkat
sql server admirer
"Jaime Rios" wrote:

> Hi folks,
> I have to create an INSERT UPDATE Trigger that pushes data from MS SQL 200
0
> to another proprietary database server. The other database server will
> occassionally push data back into MS SQL via JDBC. I don't want the MS SQL
> trigger to execute its SQL code if the INSERT UPDATE Trigger was initiated
by
> the proprietary database account. How do I capture the user or group name
in
> MS SQL? I know in PostgreSQL, you can use the "user" variable in a trigger
.
> What is the equivalent in MS SQL? Thanks in advance!!!|||Try the sytem function: system_user.
For example, SELECT system_user will provide the Domain\Loginname of the cur
rently logged in user account.
Look in Books-on-Line for more information on system_user.
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Jaime Rios" <JaimeRios@.discussions.microsoft.com> wrote in message news:EB859EAF-3AC8-4CDC-
8ECF-89A3038CC99A@.microsoft.com...
> Hi folks,
> I have to create an INSERT UPDATE Trigger that pushes data from MS SQL 200
0
> to another proprietary database server. The other database server will
> occassionally push data back into MS SQL via JDBC. I don't want the MS SQL
> trigger to execute its SQL code if the INSERT UPDATE Trigger was initiated
by
> the proprietary database account. How do I capture the user or group name
in
> MS SQL? I know in PostgreSQL, you can use the "user" variable in a trigger
.
> What is the equivalent in MS SQL? Thanks in advance!!!|||A problem with using user_name() is that it will return 'dbo' for any user
that is in the dbOwner or SA roles. That may not be as helpful as
system_user. (no parens after system_user).
System_User provides the complete loggin DOMAIN\username. It is the best
option for any form of auditing and perhaps for the purpose you have in
mind.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Venkat" <Venkat@.discussions.microsoft.com> wrote in message
news:3F000F3F-7605-4D1E-BCD1-B9452F532943@.microsoft.com...[vbcol=seagreen]
> just use user_name()
> --
> Venkat
> sql server admirer
>
> "Jaime Rios" wrote:
>sql

Sunday, March 25, 2012

Creating a Trigger on Table Access

Creating a Trigger on Access

Hi,
I am trying to create a trigger to update a datetime field when a user
logs in to their account. Is there a way to create a trigger that
updates a field when the table is accessed? The only other possible
way I can think of to accomplish this would be to write code that
updates a field on submit so that it trips the trigger I have to update
the time. This does not seem especially efficient, though.I don't think a 'SELECT' trigger would be a good idea since data could be
access for reasons other than login. IMHO, a stored procedure would be the
best approach since you can include other login activities, like password
validation. For example:
CREATE PROCEDURE dbo.usp_LoginUser
@.UserName varchar(30),
@.Password varchar(30)
AS
SET NOCOUNT ON
DECLARE @.ReturnCode int
UPDATE dbo.Users
SET LastLoginTime = GETDATE()
WHERE
UserName = @.UserName AND
Password = @.Password
IF @.@.ROWCOUNT > 0
BEGIN
--indicate login success
SET @.ReturnCode = 0
END
ELSE
BEGIN
--indicate login failure
SET @.ReturnCode = 1
END
RETURN @.ReturnCode
GO
Hope this helps.
Dan Guzman
SQL Server MVP
<iamalex84@.gmail.com> wrote in message
news:1148414912.126785.4600@.j73g2000cwa.googlegroups.com...
> Hi,
> I am trying to create a trigger to update a datetime field when a user
> logs in to their account. Is there a way to create a trigger that
> updates a field when the table is accessed? The only other possible
> way I can think of to accomplish this would be to write code that
> updates a field on submit so that it trips the trigger I have to update
> the time. This does not seem especially efficient, though.
>|||Thank you very much. I have customized and created the procedure. One
more problem though... How do I use it in ASP? I have never worked
with stored procedures before. I'm having trouble with passing the
username and password to it. Also, how do I retrieve the returned
values? Also, is there a way to grab the current value before it is
updated?
Dan Guzman wrote:[vbcol=seagreen]
> I don't think a 'SELECT' trigger would be a good idea since data could be
> access for reasons other than login. IMHO, a stored procedure would be th
e
> best approach since you can include other login activities, like password
> validation. For example:
> CREATE PROCEDURE dbo.usp_LoginUser
> @.UserName varchar(30),
> @.Password varchar(30)
> AS
> SET NOCOUNT ON
> DECLARE @.ReturnCode int
> UPDATE dbo.Users
> SET LastLoginTime = GETDATE()
> WHERE
> UserName = @.UserName AND
> Password = @.Password
> IF @.@.ROWCOUNT > 0
> BEGIN
> --indicate login success
> SET @.ReturnCode = 0
> END
> ELSE
> BEGIN
> --indicate login failure
> SET @.ReturnCode = 1
> END
> RETURN @.ReturnCode
> GO
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <iamalex84@.gmail.com> wrote in message
> news:1148414912.126785.4600@.j73g2000cwa.googlegroups.com...|||> Thank you very much. I have customized and created the procedure. One
> more problem though... How do I use it in ASP? I have never worked
> with stored procedures before. I'm having trouble with passing the
> username and password to it. Also, how do I retrieve the returned
> values? Also, is there a way to grab the current value before it is
> updated?
Below is a VBScript example as well as a proc that will return data via an
output parameter.
CREATE PROCEDURE dbo.usp_LoginUser
@.UserName varchar(30),
@.Password varchar(30),
@.LastLoginTime datetime OUTPUT
AS
SET NOCOUNT ON
DECLARE @.ReturnCode int
UPDATE dbo.Users
SET
@.LastLoginTime = LastLoginTime,
LastLoginTime = GETDATE()
WHERE
UserName = @.UserName AND
Password = @.Password
IF @.@.ROWCOUNT > 0
BEGIN
--indicate login success
SET @.ReturnCode = 0
END
ELSE
BEGIN
--indicate login failure
SET @.ReturnCode = 1
END
RETURN @.ReturnCode
GO
'see http://www.4guysfromrolla.com/webtech/110199-1.shtml for
'methods to include ADO constants in ASP
connection.Open connectionString
Set command = CreateObject("ADODB.Command")
command.ActiveConnection = connection
command.CommandType = adCmdStoredProcedure
command.CommandText = "dbo.usp_LoginUser"
Set returnCodeParameter = command.CreateParameter( _
"@.ReturnCode", adInteger, adParamReturnValue)
command.Parameters.Append returnCodeParameter
Set userNameParameter = command.CreateParameter( _
"@.UserName", adVarChar, adParamInput, 30, Request("UserName"))
command.Parameters.Append userNameParameter
Set passwordParameter = command.CreateParameter( _
"@.Password", adVarChar, adParamInput, 30, Request("Password"))
command.Parameters.Append passwordParameter
Set lastLoginTimeParameter = command.CreateParameter( _
"@.LastLoginTime", adDBTimeStamp, adParamOutput)
command.Parameters.Append lastLoginTimeParameter
command.Execute()
If returnCodeParameter.Value = 0 Then
Response.Write "Login succeeded. Last login was " &
lastLoginTimeParameter.Value
Else
Response.Write "Login failed"
End If
connection.Close
Hope this helps.
Dan Guzman
SQL Server MVP
"Alex" <iamalex84@.gmail.com> wrote in message
news:1149801803.857235.271770@.f6g2000cwb.googlegroups.com...
> Thank you very much. I have customized and created the procedure. One
> more problem though... How do I use it in ASP? I have never worked
> with stored procedures before. I'm having trouble with passing the
> username and password to it. Also, how do I retrieve the returned
> values? Also, is there a way to grab the current value before it is
> updated?
>
> Dan Guzman wrote:
>|||Ah, thank you so much. This is perfect.
Dan Guzman wrote:[vbcol=seagreen]
> Below is a VBScript example as well as a proc that will return data via an
> output parameter.
>
> CREATE PROCEDURE dbo.usp_LoginUser
> @.UserName varchar(30),
> @.Password varchar(30),
> @.LastLoginTime datetime OUTPUT
> AS
> SET NOCOUNT ON
> DECLARE @.ReturnCode int
> UPDATE dbo.Users
> SET
> @.LastLoginTime = LastLoginTime,
> LastLoginTime = GETDATE()
> WHERE
> UserName = @.UserName AND
> Password = @.Password
> IF @.@.ROWCOUNT > 0
> BEGIN
> --indicate login success
> SET @.ReturnCode = 0
> END
> ELSE
> BEGIN
> --indicate login failure
> SET @.ReturnCode = 1
> END
> RETURN @.ReturnCode
> GO
> 'see http://www.4guysfromrolla.com/webtech/110199-1.shtml for
> 'methods to include ADO constants in ASP
> connection.Open connectionString
> Set command = CreateObject("ADODB.Command")
> command.ActiveConnection = connection
> command.CommandType = adCmdStoredProcedure
> command.CommandText = "dbo.usp_LoginUser"
> Set returnCodeParameter = command.CreateParameter( _
> "@.ReturnCode", adInteger, adParamReturnValue)
> command.Parameters.Append returnCodeParameter
> Set userNameParameter = command.CreateParameter( _
> "@.UserName", adVarChar, adParamInput, 30, Request("UserName"))
> command.Parameters.Append userNameParameter
> Set passwordParameter = command.CreateParameter( _
> "@.Password", adVarChar, adParamInput, 30, Request("Password"))
> command.Parameters.Append passwordParameter
> Set lastLoginTimeParameter = command.CreateParameter( _
> "@.LastLoginTime", adDBTimeStamp, adParamOutput)
> command.Parameters.Append lastLoginTimeParameter
> command.Execute()
> If returnCodeParameter.Value = 0 Then
> Response.Write "Login succeeded. Last login was " &
> lastLoginTimeParameter.Value
> Else
> Response.Write "Login failed"
> End If
> connection.Close
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Alex" <iamalex84@.gmail.com> wrote in message
> news:1149801803.857235.271770@.f6g2000cwb.googlegroups.com...|||Ah, thank you so much. This is perfect.
Dan Guzman wrote:[vbcol=seagreen]
> Below is a VBScript example as well as a proc that will return data via an
> output parameter.
>
> CREATE PROCEDURE dbo.usp_LoginUser
> @.UserName varchar(30),
> @.Password varchar(30),
> @.LastLoginTime datetime OUTPUT
> AS
> SET NOCOUNT ON
> DECLARE @.ReturnCode int
> UPDATE dbo.Users
> SET
> @.LastLoginTime = LastLoginTime,
> LastLoginTime = GETDATE()
> WHERE
> UserName = @.UserName AND
> Password = @.Password
> IF @.@.ROWCOUNT > 0
> BEGIN
> --indicate login success
> SET @.ReturnCode = 0
> END
> ELSE
> BEGIN
> --indicate login failure
> SET @.ReturnCode = 1
> END
> RETURN @.ReturnCode
> GO
> 'see http://www.4guysfromrolla.com/webtech/110199-1.shtml for
> 'methods to include ADO constants in ASP
> connection.Open connectionString
> Set command = CreateObject("ADODB.Command")
> command.ActiveConnection = connection
> command.CommandType = adCmdStoredProcedure
> command.CommandText = "dbo.usp_LoginUser"
> Set returnCodeParameter = command.CreateParameter( _
> "@.ReturnCode", adInteger, adParamReturnValue)
> command.Parameters.Append returnCodeParameter
> Set userNameParameter = command.CreateParameter( _
> "@.UserName", adVarChar, adParamInput, 30, Request("UserName"))
> command.Parameters.Append userNameParameter
> Set passwordParameter = command.CreateParameter( _
> "@.Password", adVarChar, adParamInput, 30, Request("Password"))
> command.Parameters.Append passwordParameter
> Set lastLoginTimeParameter = command.CreateParameter( _
> "@.LastLoginTime", adDBTimeStamp, adParamOutput)
> command.Parameters.Append lastLoginTimeParameter
> command.Execute()
> If returnCodeParameter.Value = 0 Then
> Response.Write "Login succeeded. Last login was " &
> lastLoginTimeParameter.Value
> Else
> Response.Write "Login failed"
> End If
> connection.Close
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Alex" <iamalex84@.gmail.com> wrote in message
> news:1149801803.857235.271770@.f6g2000cwb.googlegroups.com...sql

Creating a trigger on a table using a cursor.

Good Day All,
I am trying to create a trigger on a table and this trigger must update
an Audit table which reflects the column name (the changes apply to),
the old value and the new value.
I have tried running through a cursor to dynamically update the Audit
table with the individual fields but this does not work since when
selecting from the inserted or deleted table one can either select all
fields or certain fields but I find it difficult to select only values
for the field that is current on my cursor.
I really will appreciate your help.
Regards,
Phonzo.I would caution against using a cursor inside a trigger.
Normally, when creating Audit trails, it is only necessary to append the
contents of deleted and/or inserted to the Audit table. And the Audit table
'should' have at least a couple of additional columns: 'WhoDoneIt' default
SYSTEM_USER, 'WhenDoneIt' default getdate().
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Phonzo" <alphonse.zulu@.treehousemis.com> wrote in message
news:1157552615.607572.152860@.m73g2000cwd.googlegroups.com...
> Good Day All,
> I am trying to create a trigger on a table and this trigger must update
> an Audit table which reflects the column name (the changes apply to),
> the old value and the new value.
>
> I have tried running through a cursor to dynamically update the Audit
> table with the individual fields but this does not work since when
> selecting from the inserted or deleted table one can either select all
> fields or certain fields but I find it difficult to select only values
> for the field that is current on my cursor.
>
> I really will appreciate your help.
>
> Regards,
> Phonzo.
>|||Hi Arnie,
Thanks a lot for this info. Much appreciated.
Thanks,
Regards,
Phonzo.
Arnie Rowland wrote:
> I would caution against using a cursor inside a trigger.
> Normally, when creating Audit trails, it is only necessary to append the
> contents of deleted and/or inserted to the Audit table. And the Audit table
> 'should' have at least a couple of additional columns: 'WhoDoneIt' default
> SYSTEM_USER, 'WhenDoneIt' default getdate().
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Phonzo" <alphonse.zulu@.treehousemis.com> wrote in message
> news:1157552615.607572.152860@.m73g2000cwd.googlegroups.com...
> > Good Day All,
> >
> > I am trying to create a trigger on a table and this trigger must update
> >
> > an Audit table which reflects the column name (the changes apply to),
> > the old value and the new value.
> >
> >
> > I have tried running through a cursor to dynamically update the Audit
> > table with the individual fields but this does not work since when
> > selecting from the inserted or deleted table one can either select all
> > fields or certain fields but I find it difficult to select only values
> > for the field that is current on my cursor.
> >
> >
> > I really will appreciate your help.
> >
> >
> > Regards,
> > Phonzo.
> >

Creating a trigger for the row being updated

Hello group,
I have a question regarding an update trigger.
For example, I have the following fields in a table..
ItemCode
ShortDescription
Quantity
UpdatedDate
UpdatedBy
I want to create a trigger that updates the "UpdatedDate" and
"UpdatedBy" fields when either the "ShortDescription" or "Quantity"
fields get updated.
I have figured out how to use the "bitwise" operators so as to target
only columns 2 and 3 for chages, however, telling the trigger to only
update the records which has been changed is proving to be a
challenge.
Thanks in advance
Leenux_tux
Try:
update m
set
UpdatedDate = getdate ()
, UpdatedBy = CURRENT_USER
from
MyTable m
join
inserted i on i.ItemCode = m.ItemCode
join
deleted d on d.ItemCode = i.ItemCode
where
i.ShortDescription <> d.ShortDescription
or i.Quantity <> d.Quantity
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"leenux_tux" <nigel.henderson@.connecta.ltd.uk> wrote in message
news:1181137271.655691.191540@.m36g2000hse.googlegr oups.com...
Hello group,
I have a question regarding an update trigger.
For example, I have the following fields in a table..
ItemCode
ShortDescription
Quantity
UpdatedDate
UpdatedBy
I want to create a trigger that updates the "UpdatedDate" and
"UpdatedBy" fields when either the "ShortDescription" or "Quantity"
fields get updated.
I have figured out how to use the "bitwise" operators so as to target
only columns 2 and 3 for chages, however, telling the trigger to only
update the records which has been changed is proving to be a
challenge.
Thanks in advance
Leenux_tux
|||leenux_tux,
Assuming that ItemCode is an unchanging primary key:
UPDATE M
SET UpdatedDate = GETDATE(), UpdatedBy = SUSER_SNAME()
FROM MyTable M
JOIN inserted i
ON M.ItemCode = i.ItemCode
The inserted table contains inserted or updated rows and the deleted table
contains deleted rows. Experience says that this trigger may actually take
more time to run for a single row than the original update ran.
If you are updating the row through a stored procedure (recommended) or
through other controllable code, considering directly supplying the data and
name in the initial update rather than having the trigger do clean up.
RLF
"leenux_tux" <nigel.henderson@.connecta.ltd.uk> wrote in message
news:1181137271.655691.191540@.m36g2000hse.googlegr oups.com...
> Hello group,
> I have a question regarding an update trigger.
> For example, I have the following fields in a table..
> ItemCode
> ShortDescription
> Quantity
> UpdatedDate
> UpdatedBy
> I want to create a trigger that updates the "UpdatedDate" and
> "UpdatedBy" fields when either the "ShortDescription" or "Quantity"
> fields get updated.
> I have figured out how to use the "bitwise" operators so as to target
> only columns 2 and 3 for chages, however, telling the trigger to only
> update the records which has been changed is proving to be a
> challenge.
> Thanks in advance
> Leenux_tux
>
|||The problem with this code is that is does not exactly meet the spec:
'I want to create a trigger that updates the "UpdatedDate" and "UpdatedBy"
fields when either the "ShortDescription" or "Quantity" fields get updated.'
IOW, if both of these columns are set to their original values, the you are
updating the UpdatedDate and UpdatedBy columns, which you don't want to do.
You need to join also on the deleted virtual table and compare the values in
inserted vs. deleted.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:uO23zPEqHHA.3948@.TK2MSFTNGP05.phx.gbl...
leenux_tux,
Assuming that ItemCode is an unchanging primary key:
UPDATE M
SET UpdatedDate = GETDATE(), UpdatedBy = SUSER_SNAME()
FROM MyTable M
JOIN inserted i
ON M.ItemCode = i.ItemCode
The inserted table contains inserted or updated rows and the deleted table
contains deleted rows. Experience says that this trigger may actually take
more time to run for a single row than the original update ran.
If you are updating the row through a stored procedure (recommended) or
through other controllable code, considering directly supplying the data and
name in the initial update rather than having the trigger do clean up.
RLF
"leenux_tux" <nigel.henderson@.connecta.ltd.uk> wrote in message
news:1181137271.655691.191540@.m36g2000hse.googlegr oups.com...
> Hello group,
> I have a question regarding an update trigger.
> For example, I have the following fields in a table..
> ItemCode
> ShortDescription
> Quantity
> UpdatedDate
> UpdatedBy
> I want to create a trigger that updates the "UpdatedDate" and
> "UpdatedBy" fields when either the "ShortDescription" or "Quantity"
> fields get updated.
> I have figured out how to use the "bitwise" operators so as to target
> only columns 2 and 3 for chages, however, telling the trigger to only
> update the records which has been changed is proving to be a
> challenge.
> Thanks in advance
> Leenux_tux
>
|||Tom,
You are correct. My mistake.
Of course, if Item_Code is NOT an unchanging primary key, then things still
get interesting.
RLF
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23qS6J8HqHHA.1216@.TK2MSFTNGP03.phx.gbl...
> The problem with this code is that is does not exactly meet the spec:
> 'I want to create a trigger that updates the "UpdatedDate" and "UpdatedBy"
> fields when either the "ShortDescription" or "Quantity" fields get
> updated.'
> IOW, if both of these columns are set to their original values, the you
> are
> updating the UpdatedDate and UpdatedBy columns, which you don't want to
> do.
> You need to join also on the deleted virtual table and compare the values
> in
> inserted vs. deleted.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:uO23zPEqHHA.3948@.TK2MSFTNGP05.phx.gbl...
> leenux_tux,
> Assuming that ItemCode is an unchanging primary key:
> UPDATE M
> SET UpdatedDate = GETDATE(), UpdatedBy = SUSER_SNAME()
> FROM MyTable M
> JOIN inserted i
> ON M.ItemCode = i.ItemCode
> The inserted table contains inserted or updated rows and the deleted table
> contains deleted rows. Experience says that this trigger may actually
> take
> more time to run for a single row than the original update ran.
> If you are updating the row through a stored procedure (recommended) or
> through other controllable code, considering directly supplying the data
> and
> name in the initial update rather than having the trigger do clean up.
> RLF
> "leenux_tux" <nigel.henderson@.connecta.ltd.uk> wrote in message
> news:1181137271.655691.191540@.m36g2000hse.googlegr oups.com...
>
|||That emphasizes the fact that a PK should never change. There are
exceptions, though.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:uh5I%23VIqHHA.3892@.TK2MSFTNGP05.phx.gbl...
Tom,
You are correct. My mistake.
Of course, if Item_Code is NOT an unchanging primary key, then things still
get interesting.
RLF
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23qS6J8HqHHA.1216@.TK2MSFTNGP03.phx.gbl...
> The problem with this code is that is does not exactly meet the spec:
> 'I want to create a trigger that updates the "UpdatedDate" and "UpdatedBy"
> fields when either the "ShortDescription" or "Quantity" fields get
> updated.'
> IOW, if both of these columns are set to their original values, the you
> are
> updating the UpdatedDate and UpdatedBy columns, which you don't want to
> do.
> You need to join also on the deleted virtual table and compare the values
> in
> inserted vs. deleted.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:uO23zPEqHHA.3948@.TK2MSFTNGP05.phx.gbl...
> leenux_tux,
> Assuming that ItemCode is an unchanging primary key:
> UPDATE M
> SET UpdatedDate = GETDATE(), UpdatedBy = SUSER_SNAME()
> FROM MyTable M
> JOIN inserted i
> ON M.ItemCode = i.ItemCode
> The inserted table contains inserted or updated rows and the deleted table
> contains deleted rows. Experience says that this trigger may actually
> take
> more time to run for a single row than the original update ran.
> If you are updating the row through a stored procedure (recommended) or
> through other controllable code, considering directly supplying the data
> and
> name in the initial update rather than having the trigger do clean up.
> RLF
> "leenux_tux" <nigel.henderson@.connecta.ltd.uk> wrote in message
> news:1181137271.655691.191540@.m36g2000hse.googlegr oups.com...
>

Creating a trigger for the row being updated

Hello group,
I have a question regarding an update trigger.
For example, I have the following fields in a table..
ItemCode
ShortDescription
Quantity
UpdatedDate
UpdatedBy
I want to create a trigger that updates the "UpdatedDate" and
"UpdatedBy" fields when either the "ShortDescription" or "Quantity"
fields get updated.
I have figured out how to use the "bitwise" operators so as to target
only columns 2 and 3 for chages, however, telling the trigger to only
update the records which has been changed is proving to be a
challenge.
Thanks in advance
Leenux_tuxTry:
update m
set
UpdatedDate = getdate ()
, UpdatedBy = CURRENT_USER
from
MyTable m
join
inserted i on i.ItemCode = m.ItemCode
join
deleted d on d.ItemCode = i.ItemCode
where
i.ShortDescription <> d.ShortDescription
or i.Quantity <> d.Quantity
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"leenux_tux" <nigel.henderson@.connecta.ltd.uk> wrote in message
news:1181137271.655691.191540@.m36g2000hse.googlegroups.com...
Hello group,
I have a question regarding an update trigger.
For example, I have the following fields in a table..
ItemCode
ShortDescription
Quantity
UpdatedDate
UpdatedBy
I want to create a trigger that updates the "UpdatedDate" and
"UpdatedBy" fields when either the "ShortDescription" or "Quantity"
fields get updated.
I have figured out how to use the "bitwise" operators so as to target
only columns 2 and 3 for chages, however, telling the trigger to only
update the records which has been changed is proving to be a
challenge.
Thanks in advance
Leenux_tux|||leenux_tux,
Assuming that ItemCode is an unchanging primary key:
UPDATE M
SET UpdatedDate = GETDATE(), UpdatedBy = SUSER_SNAME()
FROM MyTable M
JOIN inserted i
ON M.ItemCode = i.ItemCode
The inserted table contains inserted or updated rows and the deleted table
contains deleted rows. Experience says that this trigger may actually take
more time to run for a single row than the original update ran.
If you are updating the row through a stored procedure (recommended) or
through other controllable code, considering directly supplying the data and
name in the initial update rather than having the trigger do clean up.
RLF
"leenux_tux" <nigel.henderson@.connecta.ltd.uk> wrote in message
news:1181137271.655691.191540@.m36g2000hse.googlegroups.com...
> Hello group,
> I have a question regarding an update trigger.
> For example, I have the following fields in a table..
> ItemCode
> ShortDescription
> Quantity
> UpdatedDate
> UpdatedBy
> I want to create a trigger that updates the "UpdatedDate" and
> "UpdatedBy" fields when either the "ShortDescription" or "Quantity"
> fields get updated.
> I have figured out how to use the "bitwise" operators so as to target
> only columns 2 and 3 for chages, however, telling the trigger to only
> update the records which has been changed is proving to be a
> challenge.
> Thanks in advance
> Leenux_tux
>|||The problem with this code is that is does not exactly meet the spec:
'I want to create a trigger that updates the "UpdatedDate" and "UpdatedBy"
fields when either the "ShortDescription" or "Quantity" fields get updated.'
IOW, if both of these columns are set to their original values, the you are
updating the UpdatedDate and UpdatedBy columns, which you don't want to do.
You need to join also on the deleted virtual table and compare the values in
inserted vs. deleted.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:uO23zPEqHHA.3948@.TK2MSFTNGP05.phx.gbl...
leenux_tux,
Assuming that ItemCode is an unchanging primary key:
UPDATE M
SET UpdatedDate = GETDATE(), UpdatedBy = SUSER_SNAME()
FROM MyTable M
JOIN inserted i
ON M.ItemCode = i.ItemCode
The inserted table contains inserted or updated rows and the deleted table
contains deleted rows. Experience says that this trigger may actually take
more time to run for a single row than the original update ran.
If you are updating the row through a stored procedure (recommended) or
through other controllable code, considering directly supplying the data and
name in the initial update rather than having the trigger do clean up.
RLF
"leenux_tux" <nigel.henderson@.connecta.ltd.uk> wrote in message
news:1181137271.655691.191540@.m36g2000hse.googlegroups.com...
> Hello group,
> I have a question regarding an update trigger.
> For example, I have the following fields in a table..
> ItemCode
> ShortDescription
> Quantity
> UpdatedDate
> UpdatedBy
> I want to create a trigger that updates the "UpdatedDate" and
> "UpdatedBy" fields when either the "ShortDescription" or "Quantity"
> fields get updated.
> I have figured out how to use the "bitwise" operators so as to target
> only columns 2 and 3 for chages, however, telling the trigger to only
> update the records which has been changed is proving to be a
> challenge.
> Thanks in advance
> Leenux_tux
>|||Tom,
You are correct. My mistake.
Of course, if Item_Code is NOT an unchanging primary key, then things still
get interesting.
RLF
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23qS6J8HqHHA.1216@.TK2MSFTNGP03.phx.gbl...
> The problem with this code is that is does not exactly meet the spec:
> 'I want to create a trigger that updates the "UpdatedDate" and "UpdatedBy"
> fields when either the "ShortDescription" or "Quantity" fields get
> updated.'
> IOW, if both of these columns are set to their original values, the you
> are
> updating the UpdatedDate and UpdatedBy columns, which you don't want to
> do.
> You need to join also on the deleted virtual table and compare the values
> in
> inserted vs. deleted.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:uO23zPEqHHA.3948@.TK2MSFTNGP05.phx.gbl...
> leenux_tux,
> Assuming that ItemCode is an unchanging primary key:
> UPDATE M
> SET UpdatedDate = GETDATE(), UpdatedBy = SUSER_SNAME()
> FROM MyTable M
> JOIN inserted i
> ON M.ItemCode = i.ItemCode
> The inserted table contains inserted or updated rows and the deleted table
> contains deleted rows. Experience says that this trigger may actually
> take
> more time to run for a single row than the original update ran.
> If you are updating the row through a stored procedure (recommended) or
> through other controllable code, considering directly supplying the data
> and
> name in the initial update rather than having the trigger do clean up.
> RLF
> "leenux_tux" <nigel.henderson@.connecta.ltd.uk> wrote in message
> news:1181137271.655691.191540@.m36g2000hse.googlegroups.com...
>> Hello group,
>> I have a question regarding an update trigger.
>> For example, I have the following fields in a table..
>> ItemCode
>> ShortDescription
>> Quantity
>> UpdatedDate
>> UpdatedBy
>> I want to create a trigger that updates the "UpdatedDate" and
>> "UpdatedBy" fields when either the "ShortDescription" or "Quantity"
>> fields get updated.
>> I have figured out how to use the "bitwise" operators so as to target
>> only columns 2 and 3 for chages, however, telling the trigger to only
>> update the records which has been changed is proving to be a
>> challenge.
>> Thanks in advance
>> Leenux_tux
>|||That emphasizes the fact that a PK should never change. There are
exceptions, though.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:uh5I%23VIqHHA.3892@.TK2MSFTNGP05.phx.gbl...
Tom,
You are correct. My mistake.
Of course, if Item_Code is NOT an unchanging primary key, then things still
get interesting.
RLF
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23qS6J8HqHHA.1216@.TK2MSFTNGP03.phx.gbl...
> The problem with this code is that is does not exactly meet the spec:
> 'I want to create a trigger that updates the "UpdatedDate" and "UpdatedBy"
> fields when either the "ShortDescription" or "Quantity" fields get
> updated.'
> IOW, if both of these columns are set to their original values, the you
> are
> updating the UpdatedDate and UpdatedBy columns, which you don't want to
> do.
> You need to join also on the deleted virtual table and compare the values
> in
> inserted vs. deleted.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:uO23zPEqHHA.3948@.TK2MSFTNGP05.phx.gbl...
> leenux_tux,
> Assuming that ItemCode is an unchanging primary key:
> UPDATE M
> SET UpdatedDate = GETDATE(), UpdatedBy = SUSER_SNAME()
> FROM MyTable M
> JOIN inserted i
> ON M.ItemCode = i.ItemCode
> The inserted table contains inserted or updated rows and the deleted table
> contains deleted rows. Experience says that this trigger may actually
> take
> more time to run for a single row than the original update ran.
> If you are updating the row through a stored procedure (recommended) or
> through other controllable code, considering directly supplying the data
> and
> name in the initial update rather than having the trigger do clean up.
> RLF
> "leenux_tux" <nigel.henderson@.connecta.ltd.uk> wrote in message
> news:1181137271.655691.191540@.m36g2000hse.googlegroups.com...
>> Hello group,
>> I have a question regarding an update trigger.
>> For example, I have the following fields in a table..
>> ItemCode
>> ShortDescription
>> Quantity
>> UpdatedDate
>> UpdatedBy
>> I want to create a trigger that updates the "UpdatedDate" and
>> "UpdatedBy" fields when either the "ShortDescription" or "Quantity"
>> fields get updated.
>> I have figured out how to use the "bitwise" operators so as to target
>> only columns 2 and 3 for chages, however, telling the trigger to only
>> update the records which has been changed is proving to be a
>> challenge.
>> Thanks in advance
>> Leenux_tux
>

Wednesday, March 21, 2012

Creating a simple update trigger

I am extremely new at SQL Server2000 and t-sql and I'm looking to
create a simple trigger. For explanation sake, let's say I have 3
columns in one table ... Col_1, Col_2 and Col_3. The data type for
Col_1 and Col_2 are bit and Col_3 is char. I want to set a trigger on
Col_2 to compare Col_1 to Col_2 when Col_2 is updated and if they're
the same, set the value on Col_3 to "Completed". Can someone please
help me?

Thanks,
JustinHi

Posting DDL (Create table statements etc.) and example data (as Insert
statements) removes any ambiguity. Within the trigger try something like:

UPDATE t
SET Col_3 = "Completed"
FROM MyTable t JOIN INSERTED i on i.pkcol = t.pkcol
JOIN DELETED d on d.pkcol = t.pkcol
WHERE i.col2 <> d.col2
AND i.col2 = i.col1

John

"Justin" <jhosman@.numc.edu> wrote in message
news:450900ad.0407151958.73494eeb@.posting.google.c om...
> I am extremely new at SQL Server2000 and t-sql and I'm looking to
> create a simple trigger. For explanation sake, let's say I have 3
> columns in one table ... Col_1, Col_2 and Col_3. The data type for
> Col_1 and Col_2 are bit and Col_3 is char. I want to set a trigger on
> Col_2 to compare Col_1 to Col_2 when Col_2 is updated and if they're
> the same, set the value on Col_3 to "Completed". Can someone please
> help me?
> Thanks,
> Justin|||If col_3 is always based on the value of columns 1 and 2 then you don't need
it and you don't need the trigger either. Drop col_3 and calculate the
"completed" status in a view or when you query the table. Columns based on
other (non-key) columns are called Transitive Dependencies and avoiding them
is one of the goals of correct database design.

--
David Portas
SQL Server MVP
--|||David,

You're right, of course but I was just giving an example. What I
actually have is 19 columns. 18 of them are bit value 1 of them char.
The 18 represent 9 pairs. Each pair represents requested and created.
I wanted to write an update trigger on all 9 created fields that would
check to see if ALL of the requested fields were equal to thier
respective created fields and if they were, to Change the status of
field 19 from active to completed.

This is for a new user account form whereby a user would request access
to certain systems (administrated in diffrent areas) and the request
status would stay in an active state until all of the accounts were
completed by the various administrators. That way every time one
administrator created their respective account the database would check
and possible change the status of the request.

If you have any insight in this area it would be much appreciated. I
certainly need all of the help I can get.

Thanks,

Justin

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Hi John,

Thanks for your help I really appreciate it. I tried your code and I
keep getting "invalid column name" error messages. Here is my attempt
at the code...

CREATE TRIGGER CHECKSTATUS
ON dbo.useraccounttbl
FOR UPDATE AS
IF UPDATE (internetcreated)
BEGIN
UPDATE t
SET completedstatus = "Completed"
FROM useraccounttbl t join INSERTED i on i.pkcol = t.pkcol
JOIN DELETED d on d.pkcol = t.pkcol
WHERE i.internetcreated <> d.internetccreated
AND i.internetcreated = i.internetrequested
END

I'm tring to test this on these 3 columns what I will need to do on 16
more. I explained it to another person who reponded like this...

What I actually have is 19 columns. 18 of them are bit value 1 of them
char. The 18 represent 9 pairs. Each pair represents requested and
created. I wanted to write an update trigger on all 9 created fields
that would check to see if ALL of the requested fields were equal to
thier respective created fields and if they were, to Change the status
of field 19 from active to completed.

This is for a new user account form whereby a user would request access
to certain systems (administrated in diffrent areas) and the request
status would stay in an active state until all of the accounts were
created by the various administrators. That way every time one
administrator created their respective account the database would check
and possible change the status of the request.

Thanks again for all of your help.

Justin

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Hi

pkcol are the columns in the primary key, it could be more than one column
that uniquely defines each row. As there was/is no DDL it was used as short
hand.

You can pair each value

UPDATE t
SET completedstatus = "Completed"
FROM useraccounttbl t join INSERTED i on i.pkcol = t.pkcol
JOIN DELETED d on d.pkcol = t.pkcol
WHERE ( i.col1 <> d.col1
AND i.col1 = i.col2 )
OR ( i.col3 <> d.col3
AND i.col3 = i.col4 )
OR ( .......
)

John

"Justin Hosman" <hateface72@.yahoo.com> wrote in message
news:40f7dbfb$1$16414$c397aba@.news.newsgroups.ws.. .
> Hi John,
> Thanks for your help I really appreciate it. I tried your code and I
> keep getting "invalid column name" error messages. Here is my attempt
> at the code...
> CREATE TRIGGER CHECKSTATUS
> ON dbo.useraccounttbl
> FOR UPDATE AS
> IF UPDATE (internetcreated)
> BEGIN
> UPDATE t
> SET completedstatus = "Completed"
> FROM useraccounttbl t join INSERTED i on i.pkcol = t.pkcol
> JOIN DELETED d on d.pkcol = t.pkcol
> WHERE i.internetcreated <> d.internetccreated
> AND i.internetcreated = i.internetrequested
> END
> I'm tring to test this on these 3 columns what I will need to do on 16
> more. I explained it to another person who reponded like this...
> What I actually have is 19 columns. 18 of them are bit value 1 of them
> char. The 18 represent 9 pairs. Each pair represents requested and
> created. I wanted to write an update trigger on all 9 created fields
> that would check to see if ALL of the requested fields were equal to
> thier respective created fields and if they were, to Change the status
> of field 19 from active to completed.
> This is for a new user account form whereby a user would request access
> to certain systems (administrated in diffrent areas) and the request
> status would stay in an active state until all of the accounts were
> created by the various administrators. That way every time one
> administrator created their respective account the database would check
> and possible change the status of the request.
> Thanks again for all of your help.
> Justin
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||>> Here is my attempt at the code... <<

I am sure that someone will kludge something really ugly and complex
for you, but the real problem is that you are still an assembly level
programmer trying to move to an abstract high level language.

1) Stop putting that silly "-tbl" prefix on table names! This is
saying that your table models pieces of furniture that belong to user
accounts, according to ISO-11179. There is only one data structure in
SQL anyway. What you want is a name that tells us what set of
entities or relationship the table models; since it is a set,
something like "UserAccounts" is fine.

Volumns are not anything like Fields; until you know and think in
terms of rows and columns, you will never "get it" and will continue
to write BASIC in SQL.

2) SQL is a declarative language and probably the first such animal
you have ever seen. You tell it WHAT you want and it figures out HOW
to get it. This is a huge advance in programming. If the data
changes, the query stays the same but the execution plan changes with
you coding anything new.

>> What I actually have is 19 columns. 18 of them are bit value 1 of
them
char. The 18 represent 9 pairs. Each pair represents requested and
created. <<

ARRGH! Assembly language and punch card programming!! Think
relational, not physical. At best from this description, you have
nine attributes which ought to be in one column each; having "half an
atribute" is a design flaw called attribute splitting. At this point,
I have to guess at specs, but it looks like each attribute has two
values (requested and created). NEVER, NEVER, NEVER use BIT data (I
have an article due out soon in DBAzine with some of the many
reasons).

Sit down and design a status code with all the values you need; you
have
"requested" and "created", so nothing is needed for "failed" or
"rejected" in your situation?

The right way to do this is NOT with a trigger (procedural code!! The
HOW of the answer), but with a VIEW that will compute the status
(declarative code!! The WHAT of the answer).

>> This is for a new user account form whereby a user would request
access
to certain systems (administrated in diffrent areas) and the request
status would stay in an active state until all of the accounts were
created by the various administrators. <<

Now we have specs and can see that you don't know about First Normal
Form (1NF). The nine pairs are a repeating group in the data model.
Let us "flatten" out the table.

CREATE TABLE UserAccounts
(user_id INTEGER NOT NULL -- tables relate to each other, files don't
REFERENCES Personnel (user_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
admin_area INTEGER NOT NULL -- tables relate to each other, files
don't
REFERENCES Administration (admin_area)
ON DELETE CASCADE
ON UPDATE CASCADE,
request_status INTEGER DEFAULT 0 NOT NULL -- zero is "requested,not
granted"
CHECK (request_status IN (..)) -- columns have
constraints,fields don't
PRIMARY KEY (user_id, admin_area));

Your report:

CREATE VIEW RequestStatus (user_id, total_status)
AS
SELECT user_id, MIN(request_status)
FROM UserAccounts
GROUP BY user_id;

Add or drop admin areas as you wish; add more status codes easily; DRI
actions maintain the schema for you. The schema is a WHOLE, not a
collection of files that stand alone -- design the whole!|||Justin Hosman (hateface72@.yahoo.com) writes:
> Thanks for your help I really appreciate it. I tried your code and I
> keep getting "invalid column name" error messages. Here is my attempt
> at the code...
> CREATE TRIGGER CHECKSTATUS
> ON dbo.useraccounttbl
> FOR UPDATE AS
> IF UPDATE (internetcreated)
> BEGIN
> UPDATE t
> SET completedstatus = "Completed"
> FROM useraccounttbl t join INSERTED i on i.pkcol = t.pkcol
> JOIN DELETED d on d.pkcol = t.pkcol
> WHERE i.internetcreated <> d.internetccreated
> AND i.internetcreated = i.internetrequested
> END

"Completed" should be 'Completed'. SQL Server actually permits you
to use both " and ' to delimit strings, but " is only possible under
certain conditions, so stick with '.

> What I actually have is 19 columns. 18 of them are bit value 1 of them
> char. The 18 represent 9 pairs. Each pair represents requested and
> created. I wanted to write an update trigger on all 9 created fields
> that would check to see if ALL of the requested fields were equal to
> thier respective created fields and if they were, to Change the status
> of field 19 from active to completed.

A computed column would be easier:

CREATE TABLE blablabla
(pkcol ...
request1 bit NOT NULL,
completed1 bit NOT NULL,
..
status AS (CASE WHEN request1 = completed1 AND
request2 = completed2 AND
...
request9 = comepleted9 THEN 'Completed'
ELSE 'Pending'
END)

However, this is not a very good design. You should probaly change
all these columns to rows, so that if a tenth request is added, all
you need to add one more row to defining table:

CREATE TABLE requesttypes
(reqtype char(3) NOT NULL, -- mnemonic
reqname varchar(40) NOT NULL, -- descriptive
CONSTRIAINT PK_reqtypes PRIMARY KEY requesttypes (reqtype))

CREATE TABLE requests (
usrid int NOT NULL,
reqtype char(3) NOT NULL,
status char(1) NOT NULL
-- New or completed
CONSTRAINT ckc_req_status CHECK (status IN ('N', 'C')),
CONSTRAINT pk_reqs PRIMARY KEY (usrid, reqtype),
CONSTRAINT fk_regtype FOREIGN KEY (reqtype)
REFERENCES requesttypes( requtype),
CONSTRAINR fk_userd FOREIGN KEY (usrid)
REFERENCES users (usrid))

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Monday, March 19, 2012

Creating a Report to PDF in a folder?

We have created a report called "update" that we want to run every hour and place in a directory called d:\info\update.pdf

We would like to call the report from a procedure and have the proc generate the report in PDF format in the directory d:\info

Does anyone know how to do this?Try the subscription feature of Reporting Services? It will do exactly what you describe.|||Use the file share delivery method. Either create a special share with appropriate security settings, or use can also use the administrative share like this: \\localhost\d$\info, but this would required administrator credentials in the subscription.

Thursday, March 8, 2012

Creating a log file

I have a system which has several different dbases involved, and have created an update procedure within it all. It all works fine, but as a refrenence point I want to create a log file,each time the procedure is run.

This file will have useful info such as no of transactions added, no of product row n customer rows, any unmatching entries and any exception records. What I dont know is how do I create such a file?

I was thinking maybe I could save the info into a table, and then somehow transfer this into a txt file? The txt file will be replaced each time the stored procedure is run... Does any1 know any links or ideas that might help me?...I never does anything lik this b4 but I have seen it done....

Thanks in advance!found a perfect article for you
http://www.codeproject.com/useritems/Stored_Procedure_Log_File.asp

:beer: :beer: :beer:|||It might be my paranoia, but I'm not all that happy with the proposed solution. xm_cmdshell is after all among the most severe security risks in SQL Server. Would it not be a better choice to have a log TABLE instead? You could always script a command line tool to transfer the data to a text file on a regular basis.|||Thanks for the replies!!! !

roac, do you have an example site that does something like what you are sayin!!!

I really cant seem to find anything useful on the net?!|||what he meant to say is you have a log table something like

table_log
(logid identity key,
description --description will contain all relevant data you require
)

add a few more relevant columns.....

and each time then, u keep on updating this log table...so instead of having a log file u have a log table and u then need to query this table to get the description as to how many rows were inserted/updated

and his suggestion is the best way out....however if you want a log then u will have to xp_cmdshell :D|||"You could always script a command line tool to transfer the data to a text file on a regular basis."

how do i do this bit??|||"You could always script a command line tool to transfer the data to a text file on a regular basis."

how do i do this bit??
You might look at BCP, which can extract data from a table to a file. It's pretty well documented in Books Online I recall. You can use task scheduler in Windows or a SQL Server Agent Job to schedule it.|||Another alternative would be to use the SQL Server Job Agent to run the job, and include in the stored procedure/code it runs such things as "Print 'whatever data I want to log" and then have the SQL Server agent write the job output to a log.

You can use the "print" statement within the stored procedures OR the job step command window and it will be captured in the output log.

That way you can put the log anywhere on the network you want it to go, name it anything you want to name it, and not have to code anything special to create the log.

This is easily done by entering the code in the job step "command" box, similar to the following, which is a cut and past from one of our maintenance jobs.
DELETE
FROM EventLog
WHERE DATEDIFF(day, [Date], getdate()) > 90

PRINT CONVERT(varchar(10), @.@.ROWCOUNT) + ' old rows were deleted.'

DECLARE @.Count int
SELECT @.Count = (SELECT COUNT(*) FROM EventLog)
PRINT CONVERT(varchar(10), @.Count) + ' rows remain in the table.'
PRINT ' ++ JOB COMPLETE ++'
In the "advanced" tab of the step there is a place where you can enter the path/filename of the "output" file, which then becomes the log you so desperately desire.

This is the output cut and pasted from the generated log file:Job 'DailyProd- Weekly EventLog Cleanup' : Step 1, 'Delete From EventLog Where Older Than 90 Days' : Began Executing 2005-06-09 15:45:06

2244 old rows were deleted. [SQLSTATE 01000]
334 rows remain in the table. [SQLSTATE 01000]
++ JOB COMPLETE ++ [SQLSTATE 01000] and you can set up the job to overwrite the log file each time it runs, or append to the log file each time it runs.|||thanks SO much!!!

I've jsut tested it and its worked fone...
(I used the PRINT command that TallCowboy0614 spoke about)

2 questions:
1) Is there any way of inserting blank lines so that it is easier to read?
2) How do I get it to stop adding '[SQLSTATE 01000]' after each line?

Thanks again|||I think adding a PRINT ' ' (to print a blank line) will allow you that capability.

I don't think there is any way to avoid the [SQLSTATE 01000] at the end of each line, though - using this method, at least. At least if there is, I haven't been bothered by it enough to search for and/or implement it.|||'I think adding a PRINT ' ' (to print a blank line) will allow you that capability.'

I thought thats what it would be, but it didnt wrk...
also tried PRINT char(13) + char(10) but it doesnt wrk either...

:confused:|||Try a couple of them in sequence. I also have noticed sometimes stuff like that (formatting I try to do) doesn't seem to work as expected. I do get mixed results with both methods you describe though. Again, it hasn't been enough of an issue in my applications to put much thought and time into fixing it.

Tuesday, February 14, 2012

Create variables with same format in one single step?

I would need to create multiple variables with the same format for
future update, what I did is listing them separately, is there an easy
way to combine them in one step? please see query below. Thanks a lot!

--Current Query--
SELECT cast(0.0 as money) as balance_1
,cast(0.0 as money) as balance_2
,cast(0.0 as money) as balance_3
,cast(0.0 as money) as balance_4
,cast(0.0 as money) as balance_5
,cast(0.0 as money) as balance_6
,cast(0.0 as money) as balance_7
,cast(0.0 as money) as balance_8
,account_no
,XXX
INTO table1
FROM account

Can I do something like this? This one didn't work.

SELECT balance_1 to balance_8 (cast 0.0 as money)
,account_no
,XXX
INTO table1
FROM accountIt's not a single step but this might work for you:

This might work for you:

declare @.balance_1 money,
@.balance_2 money,
@.balance_3 money,
@.balance_4 money,
@.balance_5 money,
@.balance_6 money,
@.balance_7 money,
@.balance_8 money

set @.balance_1 = 0
set @.balance_2 = 0
set @.balance_3 = 0
set @.balance_4 = 0
set @.balance_5 = 0
set @.balance_6 = 0
set @.balance_7 = 0
set @.balance_8 = 0

SELECT @.balance_1,
@.balance_2,
@.balance_3,
@.balance_4,
@.balance_5,
@.balance_6,
@.balance_7,
@.balance_8,
account_no,
XXX
INTO table1
FROM account|||I'm confused. There are no variables in the code you posted - you are
creating a table with eight columns. There is only one step in a query
- the whole query operates as one logical unit.

What you seem to be looking for is a syntax shortcut that saves you
typing out the column names. The best shortcut is probably to click and
drag a list of names from the Object Browser in Query Analyzer then
search and replace the rest. I expect you could do that in much less
time than it took to write out this question. :-)

Your query itself looks a little strange. Are you sure you can't
calculate the balances with a single query rather than SELECT followed
by an UPDATE / INSERT?

--
David Portas
SQL Server MVP
--|||(rong.guo@.gmail.com) writes:
> I would need to create multiple variables with the same format for
> future update, what I did is listing them separately, is there an easy
> way to combine them in one step? please see query below. Thanks a lot!
> --Current Query--
> SELECT cast(0.0 as money) as balance_1
> ,cast(0.0 as money) as balance_2
> ,cast(0.0 as money) as balance_3
> ,cast(0.0 as money) as balance_4
> ,cast(0.0 as money) as balance_5
> ,cast(0.0 as money) as balance_6
> ,cast(0.0 as money) as balance_7
> ,cast(0.0 as money) as balance_8
> ,account_no
> ,XXX
> INTO table1
> FROM account
> Can I do something like this? This one didn't work.
> SELECT balance_1 to balance_8 (cast 0.0 as money)
> ,account_no
> ,XXX
> INTO table1
> FROM account

You could build an string with the SQL statement by iterating from
1 to 8, and use EXEC() to execute that statement. However, this is
definnitely not recommendable.

I have no idea what you are up to, but columns are usually distinct
entities. If you find that you need balance_1 to balance_8, maybe
you should make them rows instead. That is how you work with array
data in SQL, rows with pairs (index, value).

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||To answer the question I think you are trying to ask: No, there are not
arrays in SQL.

You create a table with a CREATE TABLE statement and you have to know
what it looks like before you type in the command.

You need to put the column names in the SELECT list. Using the SELECT
* option is a really bad idea for production code.

You need to give every column a meaningful name from your data model.
But fromthe look of this, you do not have a data model and are writing
code on the fly.|||Thank you ALL so much!!

I do have a bunch of other variables in the table (about 20), I only
listed account_no as an example of the variables.

Doing the calculation in a single step instead of using update is a
great idea, but for some reason, it created duplicates. I haven't found
out if it is due to the database duplicates or because of my query. I
will do further research... Thanks again...

--CELKO-- wrote:
> To answer the question I think you are trying to ask: No, there are
not
> arrays in SQL.
> You create a table with a CREATE TABLE statement and you have to know
> what it looks like before you type in the command.
> You need to put the column names in the SELECT list. Using the
SELECT
> * option is a really bad idea for production code.
> You need to give every column a meaningful name from your data model.
> But fromthe look of this, you do not have a data model and are
writing
> code on the fly.

Thursday, March 29, 2012

Creating an INSERT UPDATE Trigger that works on certain account

Creating an INSERT UPDATE Trigger that works on certain account

Sunday, March 25, 2012

Creating a Trigger on Table Access

Creating a Trigger on Table Access

Creating a Trigger on Table Access

Creating a Trigger on Access

Creating a trigger on a table using a cursor.

Creating a trigger for the row being updated

Creating a trigger for the row being updated

Wednesday, March 21, 2012

Creating a simple update trigger

Monday, March 19, 2012

Creating a Report to PDF in a folder?

Thursday, March 8, 2012

Creating a log file

Tuesday, February 14, 2012

Create variables with same format in one single step?

Creating DB from scripts

Blog Archive

About Me