Solved

HOW DO I CREATE STORED PROCEDURE / TRIGGER IN SQLSERVER 2005

Posted on 2007-12-06
5
234 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
  • 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 500 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

830 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