Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Is this correct?

Posted on 2004-10-27
9
Medium Priority
?
608 Views
Last Modified: 2008-03-03
Hi, This is difficult for me since I know very little about triggers, tables and Databases in general. But... as sometimes happens during my job I've been assigned something out of my league. Now... I've been reading and trying to "reuse" examples to do what I'm trying to do.
So, let me first explain what I'm trying to do. We have this application that runs on Solaris and uses Syabase. The user ID's are defined on a Sybase table (adb.tssuser) and on the Sun OS. I have this software that can automatically create records on both, but just that. So, I can add a user on Solaris and create a home directory. Then I can insert a record on a sybase table. What this software can't do is execute scripts or do file system operations. So, what I have in mind is that after succesfull creation on the OS and on this intermediate table a trigger would trigger and run a script that would do the rest. The script has to be called with the userid and the cat fields added. Keep in mind that I'll be writing to a intermediate table and then, with a trigger, updating the definitve table (adb.tssuser). This is what I've come up to:


USE db
SET CHAINED OFF

CREATE TABLE emp
(
      empno            NUMERIC(8)      IDENTITY,
      usrid            NVARCHAR(64) NULL,
      cat            CHAR(64) NULL,
      CONSTRAINT pk_empno PRIMARY KEY (empno),
)
GO


/* holds last generated identity value */
CREATE TABLE empno
(
      empno      NUMERIC(8)      NOT NULL,
      CONSTRAINT unq_empno UNIQUE (empno)
)
INSERT INTO empno VALUES(-1)
GO

CREATE PROCEDURE sp_empno
      @io_empno      NUMERIC OUTPUT,
      @i_userid       VARCHAR
AS

DECLARE @key NUMERIC(8)

IF (@io_empno IS NULL)
BEGIN

      DECLARE rs CURSOR FOR SELECT empno FROM empno
      OPEN rs
      FETCH rs INTO @key
      IF(@key = -1)
            SELECT @io_empno = NULL
      ELSE
            SELECT @io_empno = @key
      CLOSE rs
      DEALLOCATE CURSOR rs
END
GO
sp_procxmode sp_empno, "anymode"
GO


/****************************************************************************/
/* Create event log table.                                                  */
/****************************************************************************/

CREATE TABLE eventlog
(
      record_id      NUMERIC(38)      IDENTITY
                              NOT NULL,
      table_key      VARCHAR(64)      NOT NULL,
      status      CHAR(1)      DEFAULT 'N',
      event_type      NUMERIC(1)      NOT NULL
                              CHECK (event_type IN (1, 2, 3, 4, 5, 6, 7, 8)),
      event_time      DATETIME      DEFAULT getDate()
                              NOT NULL,
      perpetrator      VARCHAR(64)      DEFAULT USER
                              NULL,
      table_name      VARCHAR(64)      NOT NULL,
      column_name      VARCHAR(64) NULL,
      old_value      NVARCHAR(64) NULL,
      new_value      NVARCHAR(64) NULL
)

CREATE UNIQUE NONCLUSTERED INDEX i_eventlog_1 ON eventlog(table_key, record_id, status)
CREATE UNIQUE NONCLUSTERED INDEX i_eventlog_2 ON eventlog(record_id, table_key)
GO


CREATE TRIGGER t_emp_insert
      ON emp
      FOR INSERT
AS

DECLARE
      @opcode            SMALLINT,
      @date_style       SMALLINT,
      @table_name       VARCHAR(64),
      @key                   VARCHAR(64),
      @new_empno            VARCHAR(64)

      UPDATE empno SET empno = (SELECT new.empno FROM inserted new)

      SELECT @opcode       = 5      /* Insert (Query-Back) */
      SELECT @date_style = 109  /* mon dd yyy hh:mm:sss AM (or PM) */
      SELECT @table_name = 'emp'

      SELECT @new_empno = LTRIM(STR(new.empno)) from inserted new
      SELECT @key = "empno=" + @new_empno

      INSERT INTO dbo.eventlog(event_type, table_name, table_key)
            VALUES(@opcode, @table_name, @key)
GO

CREATE TRIGGER t_emp_delete
    ON emp
    FOR DELETE

AS

      INSERT INTO dbo.eventlog(event_type, table_name, table_key)
            (SELECT 4, 'emp', 'empno=' + LTRIM(STR(old.empno)) from deleted old)
GO
CREATE TRIGGER t_emp_scp_add
      ON emp
      FOR INSERT
AS

DECLARE
      @opcode            SMALLINT,
      @date_style       SMALLINT,
      @table_name       VARCHAR(64),
      @key                   VARCHAR(64),
      @new_empno            VARCHAR(64)

      SELECT @opcode       = 5      /* Insert (Query-Back) */
      SELECT @date_style = 109  /* mon dd yyy hh:mm:sss AM (or PM) */
      SELECT @table_name = 'emp'

      SELECT @new_empid = STR(new.userid) from inserted new
      SELECT @categ = STR(new.cat) from inserted new
        select @script = "/home/nvl/oss_novell.sh " + @new_empid + " " + @categ
        execute xp_cmdshell @script
GO


Thanks for your help!!!
Nico
0
Comment
Question by:nickoarg
[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
  • 4
  • 3
9 Comments
 
LVL 3

Author Comment

by:nickoarg
ID: 12423390
Note that there might be things and commands that shouldn't and things that do nothing... this is due to my lack of DB knowledge...
0
 
LVL 6

Expert Comment

by:ChrisKing
ID: 12431364
your triggers are written as if you will only ever be processing one row at a time (common mistake)

you should also know that xp_cmdshell can not be rolled back, so :

begin tran
insert into emp ...
rollback

will cal the /home/nvl/oss_novell.sh script but you won't have the row in te database
0
 
LVL 3

Author Comment

by:nickoarg
ID: 12436937
The idea is that only one row at a time can be inserted, never more than 1 in one operation.

..."but you won't have the row in te database"...
Why? Does the trigger acts before the row is inserted in the table "emp"?

"begin tran
insert into emp ...
rollback"
please, note that I really don't know about databases... I've base all that on examples and existing code... where do I put that? how should it look to be correct?
thanks
0
Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

 
LVL 6

Expert Comment

by:ChrisKing
ID: 12439311
> The idea is that only one row at a time can be inserted, never more than 1 in one operation.
that idea might be OK for this week, but next month or next year or later, someone will insert more than one row and the triggers will not function as expected

to protect yourself (asssuming you don want to rewrite them), put the following code into the top of the triggers.

IF @@rowcount > 1
  BEGIN
    ROLLBACK TRIGGER WITH RAISERROR <put your error details here>
    RETURN
  END

> Why? Does the trigger acts before the row is inserted in the table "emp"?
the trigger does not  act before the row is inserted, the rollback will force everything that was just inserted/updated/deleted to be undone, but you can not undo a execution of a unix script. This is a known limitation of the xp commands.
0
 
LVL 3

Author Comment

by:nickoarg
ID: 12439443
"someone will insert more than one row and the triggers will not function as expected"
no..., the updates come from a software that updates only one row at a time, but I undestand what you say.

I still don't understand the rollback...
step 1, a new row is inserted in the table emp
step 2, the trigger t_emp_scp_add fires away and the xp_cmdshell would run my script

why do I want to remove new the row from table emp? (assuming that that is what rollback does)
0
 
LVL 6

Accepted Solution

by:
ChrisKing earned 2000 total points
ID: 12442505
in simple terms ROLLBACK means don't keep the changes I just made, whereas COMMIT means keep the changes
To better understand it, read up on "transaction control"

try the following script:

CREATE TABLE #test (num int NOT NULL)

BEGIN TRAN
INSERT INTO #test SELECT 1
INSERT INTO #test SELECT 2
INSERT INTO #test SELECT 3
COMMIT
SELECT * FROM #test                     -- will show 1, 2 and 3

BEGIN TRAN
INSERT INTO #test SELECT 4
INSERT INTO #test SELECT 5
SELECT * FROM #test                     -- will show 1, 2, 3, 4 and 5
ROLLBACK
SELECT * FROM #test                     -- will show 1, 2 and 3

BEGIN TRAN
UPDATE #test SET num = num + 100
SELECT * FROM #test                     -- will show 101, 102 and 103
ROLLBACK
SELECT * FROM #test                     -- will show 1, 2 and 3

BEGIN TRAN
DELETE FROM #test
SELECT * FROM #test                     -- will show no rows
ROLLBACK
SELECT * FROM #test                     -- will show 1, 2 and 3

DROP TABLE #test
go

===============
actual script output
===============
(1 row affected)
(1 row affected)
(1 row affected)
 num
 -----------
           1
           2
           3

(3 rows affected)
(1 row affected)
(1 row affected)
 num
 -----------
           1
           2
           3
           4
           5

(5 rows affected)
 num
 -----------
           1
           2
           3

(3 rows affected)
(3 rows affected)
 num
 -----------
         101
         102
         103

(3 rows affected)
 num
 -----------
           1
           2
           3

(3 rows affected)
(3 rows affected)
 num
 -----------

(0 rows affected)
 num
 -----------
           1
           2
           3

(3 rows affected)
0
 
LVL 6

Expert Comment

by:ChrisKing
ID: 12618837
I am suprised:
- this question was not even pinged
- and no answer is actually marked as accepted
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Geo-targeting is the practice of distributing content based on a person’s location, as best as you can determine it. Let’s look at some ways you could successfully use this tactic. The following tips and case studies could lead to meaningful results.
Let's take a look into the basics of ransomware—how it spreads, how it can hurt us, and why a disaster recovery plan is important.
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

610 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