Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

HOW DO I CREATE STORED PROCEDURE / TRIGGER IN SQLSERVER 2005

Posted on 2007-12-06
5
Medium Priority
?
240 Views
Last Modified: 2010-04-21
My questions is the following. I have an MS access program that allows users to enter detailed event logs. However, I  want to create a trigger in MSSQL SERVER 2005 and be able to update the table T_LOG based on the event type added at T_LogEvents.  

So for example. If a created the following record in T_LOGEVENTS...
-EventID: 05513546 (this is an autonumber)
-EventType: DOCUMENT
-Dn_number: EP071105001  
-EventDate: 12/12/07 11:53
-LogUser: epicazo
-LogAction: NOTIFIED
-ToFrom:  Paula Jones

I want to the trigger in SQL2005 to acknowledge the add in T_LOGEVENTS, and then update the record in T_LOG, lets say SELECT   DN_NUMBER FROM  T_Log WHERE (DN_NUMBER = N'EP071105001'), and if column DBO.T_LOGEVENTS.LogAction ="NOTIFIED", then the trigger will update the T_LOG record with the following ... dbo.T_LOG.enotify=dbo.T_LogEvents.EventDate, AND T_Log.eLastEventBy=dbo.T_LogEvents.LogUser

Below are my tables.  There is a relation of one (t_log) to many (t_logevents)
<<<<PARENT>>>>TABLE: dbo.t_log
...COLUMNS...
-DN_NUMBER...(PK) nvarchar(20)
-inituser...nvarchar(15)
-initdate...datetime
-eNotify...datetime
-eLastEvent...datetime
-eLastEventBy...NVARCHAR(15)

<<<<CHILD>>>>TABLE: dbo.t_LogEvents
...COLUMNS...
-EventId...(PK) numeric(18,0)... identity increment 1
-EventType...nvarchar(20)
-Dn_number...nvarchar(20)
-EventDate...datetime
-LogUser...nvarchar(15)
-LogAction...nvarchar(25)
-ToFrom...nvarchar(30)


Obviously the syntax is not correct, but I am so green in trigger, never dealt with them, so I'll need a step by step on how to create this trigger.

THANKS
0
Comment
Question by:epicazo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 18

Expert Comment

by:Yveau
ID: 20423072
create trigger:
http://msdn2.microsoft.com/en-us/library/ms189799.aspx

create stored procedure:
http://msdn2.microsoft.com/en-us/library/ms187926.aspx

... and a trigger is just as a stored procedure. Only thing is that you can't directly run it, the RDBMS will ... One other thing, you have two 'virtual' tables in the triggers: Inserted (in insert and update trigger) and Deleted (in update and delete trigger) to help you deal with the records before the DML hit the table ...

Hope this helps ...
0
 
LVL 25

Accepted Solution

by:
imitchie earned 2000 total points
ID: 20423082
create trigger trigger_T_logevents
on T_logevents
after insert
as
begin

update tl set
  enotify=inserted.EventDate,
  eLastEventBy=inserted.LogUser
from dbo.t_log tl
  inner join inserted on tl.DN_NUMBER = inserted.DN_NUMBER
where inserted.LogAction ='NOTIFIED'

end
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20423092
Run the above code from Query Analyzer or SSMStudio
0
 

Author Comment

by:epicazo
ID: 20429207
This is perfect THANK YOU so much.  Two (well three) additional questions -- if you don't mind:
Will all my triggers have the "update tl set" string?
Why name it TL and not anything else -- what exactly is it doing here

Also, could you recommend additional resources books to learn more about triggers and stored procedures?  I want to learn more of this stuff -- it's awesome!


0
 

Author Closing Comment

by:epicazo
ID: 31413254
:)
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question